CODEWITHSUNDEEP
vbaexcel
Jack Casas
Jack Casas

Reputation: 984

Time to rounded minutes

I have this values in Excel cells:

 Time        Dev         Total
 00:47:53    00:03:40     50

I'm using VBA to calculate my "Total" value:

 Minute(Cells(2, 1).Value) + (Minute(Cells(2, 2).Value))

This returns 50. I want to to take in consideration the seconds, and return 52, as the seconds would ad up to 93 extra seconds, which make more than a minute and a half. So it must round up to 52

How must I adjust my VBA code?

Upvotes: 0

Views: 1268

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

The VBA Minute function will always truncate, and its return values are limited to integers in the range 0...59.

Try this instead:

Function sumMinutes(d1 As Date, d2 As Date) As Long
    sumMinutes = CLng((d1 + d2) * 1440)
End Function

Excel stores dates as days and fractions of a day. Since there are 1440 minutes in a day, multiplying the Excel date by 1440 will give the number of minutes. Using CLng then effectively rounds the value.

EDIT: Note that because the VBA rounding algorithm rounds to the nearest even number (so-called bankers rounding), some times that fall EXACTLY at the 30 second mark, may round down instead of up. If this is an issue for you, instead of using the VBA CLng, you can use the WorksheetFunction.Round method.

Upvotes: 2

Related Questions