Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Decimal Minutes to Hours-Minutes-Seconds
The below formula will convert decimal minutes-seconds into real time
hours-minutes-seconds. In the current example, the total time is 23:59:00 which is correct. However, if I increase the minutes so that they total more than 24 hours, my formula losses 24 hours. Can someone help me modify my formula, so if cell A2 changes to 2 decimal minutes, the correct total would be displayed as 24:01:00, instead of 00:01:00 ? Example Formula ***************** =TEXT((SUM(A1:A5))/1440, "hh:mm:ss") Example Data ***************** A =============== 1 200.00 2 0.00 3 39.00 4 800.00 5 400.00 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Decimal Minutes to Hours-Minutes-Seconds
=TEXT((SUM(A1:A5))/1440,"[hh]:mm:ss")
-- Jim "Scott" wrote in message ... The below formula will convert decimal minutes-seconds into real time hours-minutes-seconds. In the current example, the total time is 23:59:00 which is correct. However, if I increase the minutes so that they total more than 24 hours, my formula losses 24 hours. Can someone help me modify my formula, so if cell A2 changes to 2 decimal minutes, the correct total would be displayed as 24:01:00, instead of 00:01:00 ? Example Formula ***************** =TEXT((SUM(A1:A5))/1440, "hh:mm:ss") Example Data ***************** A =============== 1 200.00 2 0.00 3 39.00 4 800.00 5 400.00 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Decimal Minutes to Hours-Minutes-Seconds
what exactly do brackets around the hours mean?
"Jim Rech" wrote in message ... =TEXT((SUM(A1:A5))/1440,"[hh]:mm:ss") -- Jim "Scott" wrote in message ... The below formula will convert decimal minutes-seconds into real time hours-minutes-seconds. In the current example, the total time is 23:59:00 which is correct. However, if I increase the minutes so that they total more than 24 hours, my formula losses 24 hours. Can someone help me modify my formula, so if cell A2 changes to 2 decimal minutes, the correct total would be displayed as 24:01:00, instead of 00:01:00 ? Example Formula ***************** =TEXT((SUM(A1:A5))/1440, "hh:mm:ss") Example Data ***************** A =============== 1 200.00 2 0.00 3 39.00 4 800.00 5 400.00 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Decimal Minutes to Hours-Minutes-Seconds
Try this on a new worksheet:
Format B1: hh:mm:ss Format C1: [hh]:mm:ss Put =a1 in both cells Now put some times in A1 to see what appears in B1 and C1. Try 13:00:00 18:00:00 23:00:00 38:00:00 43:00:00 And you'll see what those []'s do. Scott wrote: what exactly do brackets around the hours mean? "Jim Rech" wrote in message ... =TEXT((SUM(A1:A5))/1440,"[hh]:mm:ss") -- Jim "Scott" wrote in message ... The below formula will convert decimal minutes-seconds into real time hours-minutes-seconds. In the current example, the total time is 23:59:00 which is correct. However, if I increase the minutes so that they total more than 24 hours, my formula losses 24 hours. Can someone help me modify my formula, so if cell A2 changes to 2 decimal minutes, the correct total would be displayed as 24:01:00, instead of 00:01:00 ? Example Formula ***************** =TEXT((SUM(A1:A5))/1440, "hh:mm:ss") Example Data ***************** A =============== 1 200.00 2 0.00 3 39.00 4 800.00 5 400.00 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to convert 157.25 minutes, into hours, minutes and seconds. | Excel Discussion (Misc queries) | |||
convert decimal figure to minutes and seconds | Excel Discussion (Misc queries) | |||
How do I convert hours, minutes, and seconds into decimal format? | Excel Worksheet Functions | |||
Convert Decimal hours and Minutes to minutes please. | Excel Worksheet Functions | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel |