Home |
Search |
Today's Posts |
#1
|
|||
|
|||
time formula
Hi all,
(office 97) You guys helped me the other day with some formula where I wanted to convert say a figure of 93 into seconds. This was done & works fine, the formula was as follows; In cell A2 enter =INT(A1/60) + MOD(A1,60)/100 I now need help changing the format slightly. Say for example there was a figure of 3666, I need to make the formula diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs Have set the cell format to the above. If you have any ideas it would be appreciated as I am out of my depth Thanks again in advance |
#2
|
|||
|
|||
Hi
when 3666 is in cell A2 Into another cell enter the formula =A2/(24*60*60) and format as "hh:mm:ss" (or "[h]:mm:ss" when time can override 24h) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "James" wrote in message ... Hi all, (office 97) You guys helped me the other day with some formula where I wanted to convert say a figure of 93 into seconds. This was done & works fine, the formula was as follows; In cell A2 enter =INT(A1/60) + MOD(A1,60)/100 I now need help changing the format slightly. Say for example there was a figure of 3666, I need to make the formula diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs Have set the cell format to the above. If you have any ideas it would be appreciated as I am out of my depth Thanks again in advance |
#3
|
|||
|
|||
Arvi,
Thanks for that - 1 last thing can you explain how this formula works, ie; are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the number of seconds in cell A2? "Arvi Laanemets" wrote: Hi when 3666 is in cell A2 Into another cell enter the formula =A2/(24*60*60) and format as "hh:mm:ss" (or "[h]:mm:ss" when time can override 24h) -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "James" wrote in message ... Hi all, (office 97) You guys helped me the other day with some formula where I wanted to convert say a figure of 93 into seconds. This was done & works fine, the formula was as follows; In cell A2 enter =INT(A1/60) + MOD(A1,60)/100 I now need help changing the format slightly. Say for example there was a figure of 3666, I need to make the formula diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs Have set the cell format to the above. If you have any ideas it would be appreciated as I am out of my depth Thanks again in advance |
#4
|
|||
|
|||
Hi
"James" wrote in message ... Arvi, Thanks for that - 1 last thing can you explain how this formula works, ie; are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the number of seconds in cell A2? Exctly that. Date and time in Excel are really both same data type, a datetime. The date is integer part od datetime, and time is decimal part. One day is really a number 1. You had the number of seconds in cell, dividing it with 60 give number of minutes, dividing again gives number of hours, and at last dividing with 24 gives number of days (in your case it is 0.0424305555555556, which in time format is displayed as 01:01:06). PS. I used (24*60*60) in formula to make it easier to understand. You can replace it with 86400. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#5
|
|||
|
|||
Thankyou- I understand
Cheers "Arvi Laanemets" wrote: Hi "James" wrote in message ... Arvi, Thanks for that - 1 last thing can you explain how this formula works, ie; are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the number of seconds in cell A2? Exctly that. Date and time in Excel are really both same data type, a datetime. The date is integer part od datetime, and time is decimal part. One day is really a number 1. You had the number of seconds in cell, dividing it with 60 give number of minutes, dividing again gives number of hours, and at last dividing with 24 gives number of days (in your case it is 0.0424305555555556, which in time format is displayed as 01:01:06). PS. I used (24*60*60) in formula to make it easier to understand. You can replace it with 86400. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for averaging times | Excel Discussion (Misc queries) | |||
How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
Formula for time? | Excel Discussion (Misc queries) | |||
need help with time formula | Excel Worksheet Functions | |||
Time / Formula to look at time difference | Excel Worksheet Functions |