Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converting decimals into hh:mm
Hi, can someone please help. I have employees data regarding hours worked in a decimal format but need to convert this to show hh:mm. So that 3.5 becomes 3:30. I have tried dividing the original decimal by 24 and reformatting but this only works for figures up to 24. How would I be able to say covert 103.25 hours into 103:15. Is there a way of doing this? Many thanks, Andrew -- Shandy720 ------------------------------------------------------------------------ Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230 View this thread: http://www.excelforum.com/showthread...hreadid=474998 |
#2
|
|||
|
|||
Hi
The formula is same, but use format "[h]:mm" -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Shandy720" wrote in message ... Hi, can someone please help. I have employees data regarding hours worked in a decimal format but need to convert this to show hh:mm. So that 3.5 becomes 3:30. I have tried dividing the original decimal by 24 and reformatting but this only works for figures up to 24. How would I be able to say covert 103.25 hours into 103:15. Is there a way of doing this? Many thanks, Andrew -- Shandy720 ------------------------------------------------------------------------ Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230 View this thread: http://www.excelforum.com/showthread...hreadid=474998 |
#3
|
|||
|
|||
thanks avri but i dont seem to have the choice for that format. I can convert it to (h) mm:ss but ideally do not want to have seconds shown. The trouble i am having is regarding adding hours up in the first place. I have columns of hours worked each day and sum this for the weekly total. However using an hh:mm format does not allow me to sum it up properly. For instance 9:10+9:10+9:10+9:10 equals 12:40. Therefore i convert the hours worked into a decimal so they can be summed and then need to convert them back into a total weekly hours worked later in the hh:mm original format!!!! I hope this makes sense and many thanks for your help. -- Shandy720 ------------------------------------------------------------------------ Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230 View this thread: http://www.excelforum.com/showthread...hreadid=474998 |
#4
|
|||
|
|||
Hi
Simply select Custom format, and enter the format string into Type field. Arvi Laanemets "Shandy720" wrote in message ... thanks avri but i dont seem to have the choice for that format. I can convert it to (h) mm:ss but ideally do not want to have seconds shown. The trouble i am having is regarding adding hours up in the first place. I have columns of hours worked each day and sum this for the weekly total. However using an hh:mm format does not allow me to sum it up properly. For instance 9:10+9:10+9:10+9:10 equals 12:40. Therefore i convert the hours worked into a decimal so they can be summed and then need to convert them back into a total weekly hours worked later in the hh:mm original format!!!! I hope this makes sense and many thanks for your help. -- Shandy720 ------------------------------------------------------------------------ Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230 View this thread: http://www.excelforum.com/showthread...hreadid=474998 |
#5
|
|||
|
|||
Hi again
"Shandy720" wrote in message ... thanks avri but i dont seem to have the choice for that format. I can convert it to (h) mm:ss but ideally do not want to have seconds shown. The trouble i am having is regarding adding hours up in the first place. I have columns of hours worked each day and sum this for the weekly total. However using an hh:mm format does not allow me to sum it up properly. For instance 9:10+9:10+9:10+9:10 equals 12:40. Format the cell with sum as "[h]:mm" , and same result is displayed as 36:40. To understand why, format same cell as "d h:mm" - now 1 12:40 is displayed. The reason for such behaviour is the way dates and times are stored in Excel. Format the same cell with sum as Numeric or General - 1.5277778 id displayed. Integer part of this number is for days (time intervals 24 hours long), decimal part is for hours, minutes and seconds, and is calculated as 1/24 of hour. I.e. 12 hours and 40 minutes is stored as (12+40/60)/24~0.5277778 Using square bracets around leftmost part of time format string supresses 24-hour or 60-minute or 60-second rollower. I.e formats "[h]", "[h]:mm:ss", "[m]:ss" etc. are supported. Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting CSV files into Excel files | Excel Worksheet Functions | |||
How do I calculate without decimals in excel? | Excel Worksheet Functions | |||
Converting imported numbers to decimals (rather than /100) | Excel Worksheet Functions | |||
Decimals separation by . or , | Excel Discussion (Misc queries) | |||
roundoff when converting text to numbers | Excel Worksheet Functions |