CODEWITHSUNDEEP
excelvba
Ryan Welsh
Ryan Welsh

Reputation: 181

Convert Time Values (In-Cell) to Minutes - Excel VBA

I am trying to take the values of time passed (formatted HH:MM:SS) and convert it to just minutes.

The issue I am having is that when I try and get the value of the time-value cell, it converts it to some odd value.

Example:

Wrong Value (what Excel gives me now, in worksheet): 34:32:12 = 1.43902777777778

Right Value (what Excel should give me): 34:32:12 = 2072.2

Calcualted:

34*60 + 32 + 12/60

Upvotes: 1

Views: 2379

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

Assuming your source value is in cell A1, here is all you need:

=N(A1*1440)

This method does not require a reformatting of the output cell.

How does it work?

Dates and times are stored in Excel as a combined number... where the integer portion represents the number of days since December 31, 1899 (although the year that Excel calculates from can be changed to 1904 in the Excel Options, but that is immaterial).

The decimal portion of the stored number represents the time component to associate with the date.

Your value of 1.43902777777778 is correct. It states that the ~34.5 hours represents ~1.44 days.

Since you are interested in minutes, we convert that days figure to minutes by multiplying by 1440 as there are 1440 minutes in a day.

The N() function that wraps that calculation ensures that the displayed output is treated as numeric by Excel. Otherwise the output cell would adopt the date-formatting of A1.

Upvotes: 2

shg
shg

Reputation: 333

Either format the cell as [m] to see 2072, or multiply by 1440 (the number of minutes in a day) and format as 0.0 to see 2072.2

Upvotes: 0

Related Questions