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

Question

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

See http://stackoverflow.com/questions/15763097/how-to-prevent-excel-from-handling-strings-containing-a-colon-as-formulas — John Martinelli, Jul 19 '15 at 17:43

Excel Hero · Accepted Answer · 2015-07-19T18:24:14.020

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.

Good edit by the way - I was just about to ask how the functions calculates everything. Very useful. — Ryan Welsh, Jul 19 '15 at 18:37

score 0 · Answer 2 · answered Jul 19 '15 at 18:11

0

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

answered Jul 19 '15 at 18:11

shg

323
2
7

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

2 Answers2