Originally Posted by
junada0
When I first tried I got 00:00:00 on all 3 functions.
Yes, if formatted as Time that it is to be expected given HOUR, MINUTE & SECOND return Integers and in XL Integers equate to Days, Time is Decimal.
To elaborate by means of Example:
B1 will show as 0.5 - this is because 12 hours equates to 1/2 a day (as 6 am is 0.25 and 6pm = 0.75)
C1 will show as 12 as the Hour is extracted as an Integer.
If C1 is formatted to say h:mm:ss it will show as 0:00:00 given 12 in XL DateTime terms equates to 12 days (288 hours) and once full days have been accounted for there is no Time remainder.
Were you to format the cell as [h]:mm:ss you would see it return 288:00:00 - ie the cumulative hours would be displayed.
The above has probably confused you more but in essence the key is that HOUR, MINUTE & SECOND return non-time values and the results should thus be formatted as General / Number and not Time.
Bookmarks