Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
I use excel spreadsheet for entering my time worked. I have it currently set
to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
dwhapp wrote:
I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
In case this helps any, there is a formula that can be used to replace your
chart. For the result as text, this... =TEXT(ROUNDUP(A1/60,1),".0") And for the result as a number, this... =--TEXT(ROUNDUP(A1/60,1),".0") where A1 is assumed to contain the number of minutes from 1 to 60. -- Rick (MVP - Excel) "dwhapp" wrote in message ... I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1)
That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Typo:
And here's a weird one: 1:06 PM = 1.1 Should be: 1:06 PM = 13.1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
=INT(A1*24)+CEILING(MINUTE(A1)/60,0.1)
We can reduce it by one calculation cycle by replacing INT: =HOUR(A1)+CEILING(MINUTE(A1)/60,0.1) I wonder why I don't see these things the first time around! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
I'm not entirely convinced the OP has the hours and minutes bundled up into
a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Hmmm...
have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
I sort of read of that quickly and took it to mean the OP knew how to form a
time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Try this:
=ROUNDUP(A1*24,1) "dwhapp" wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
T. Valko wrote:
=INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 You're right, I tested this on a limited sample and didn't notice any discrepancies. Check out these results!!! 12:59 PM 13.0 1:00 PM 12.9 1:01 PM 13.1 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Maybe I should explain my question better. Suppose I enter my start time
8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
So what answers do you get from Rick's formula with your data? For me, his
formula gives 8.3, 8.8, and 9.0 in the cases where you wanted 8.3, 8.8, and 9.0. I therefore fail to see your problem. -- David Biddulph "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1,
then you would use either of these two formulas to calculate the difference and apply your chart at the same time... Rick's Formula ======================== =HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1) Biff's Formula ======================== =HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1) One note though... the values in the calculated column are what you see, so when you add them up, you are adding up the rounded values, not the actually calculated differences. So, for your example, the 8 hours 15 minutes difference, which actually calculates to 8.25, will be added as 8.3 (not 8.25) when summed up. -- Rick (MVP - Excel) "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Try this:
If your end times will never span past midnight: A1 = start time = 8:45 B1 = end time = 17:00 =IF(COUNT(A1:B1)<2,"",CEILING((B1-A1)*24,0.1)) If your end times might span past midnight: A1 = start time = 23:00 B1 = end time = 7:15 =IF(COUNT(A1:B1)<2,"",CEILING(MOD(B1-A1,1)*24,0.1)) -- Biff Microsoft Excel MVP "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Im assuming the B1-A1 reference concerns the end time minus the start time or
am I wrong. Finally, what happens if I add in two additional boxes for lunch start time and lunch end time whereas: A1 B1 C1 D1 start lunch start lunch end end 8:00 12:00 12:30 17:15 Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total time worked for the day. "Rick Rothstein" wrote: Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1, then you would use either of these two formulas to calculate the difference and apply your chart at the same time... Rick's Formula ======================== =HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1) Biff's Formula ======================== =HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1) One note though... the values in the calculated column are what you see, so when you add them up, you are adding up the rounded values, not the actually calculated differences. So, for your example, the 8 hours 15 minutes difference, which actually calculates to 8.25, will be added as 8.3 (not 8.25) when summed up. -- Rick (MVP - Excel) "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Just put that formula call into the two places I put the simpler formula in
earlier... Rick's Formula ======================== =HOUR(B1-A1+D1-C1)+ROUNDUP(MINUTE(B1-A1+D1-C1)/60,1) Biff's Formula ======================== =HOUR(B1-A1+D1-C1)+CEILING(MINUTE(B1-A1+D1-C1)/60,0.1) -- Rick (MVP - Excel) "dwhapp" wrote in message ... Im assuming the B1-A1 reference concerns the end time minus the start time or am I wrong. Finally, what happens if I add in two additional boxes for lunch start time and lunch end time whereas: A1 B1 C1 D1 start lunch start lunch end end 8:00 12:00 12:30 17:15 Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total time worked for the day. "Rick Rothstein" wrote: Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1, then you would use either of these two formulas to calculate the difference and apply your chart at the same time... Rick's Formula ======================== =HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1) Biff's Formula ======================== =HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1) One note though... the values in the calculated column are what you see, so when you add them up, you are adding up the rounded values, not the actually calculated differences. So, for your example, the 8 hours 15 minutes difference, which actually calculates to 8.25, will be added as 8.3 (not 8.25) when summed up. -- Rick (MVP - Excel) "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time to decimal format
Never mind I got it. Thanks a lot guys for all your help. Now I can apply
this to all my hourly employees so I don't have to spend hours converting it myself. "dwhapp" wrote: Im assuming the B1-A1 reference concerns the end time minus the start time or am I wrong. Finally, what happens if I add in two additional boxes for lunch start time and lunch end time whereas: A1 B1 C1 D1 start lunch start lunch end end 8:00 12:00 12:30 17:15 Currently I have the formula set at =(B1-A1)+(D1-C1) to give me the total time worked for the day. "Rick Rothstein" wrote: Assuming your Start Time (8:45) is in A1 and your End Time (17:00) is in B1, then you would use either of these two formulas to calculate the difference and apply your chart at the same time... Rick's Formula ======================== =HOUR(B1-A1)+ROUNDUP(MINUTE(B1-A1)/60,1) Biff's Formula ======================== =HOUR(B1-A1)+CEILING(MINUTE(B1-A1)/60,0.1) One note though... the values in the calculated column are what you see, so when you add them up, you are adding up the rounded values, not the actually calculated differences. So, for your example, the 8 hours 15 minutes difference, which actually calculates to 8.25, will be added as 8.3 (not 8.25) when summed up. -- Rick (MVP - Excel) "dwhapp" wrote in message ... Maybe I should explain my question better. Suppose I enter my start time 8:45 and end time 17:00. Excel will return a total time worked for the day as 8:15. If I work another day from 8:00 to 17:45 then it will give me 8:45. Once I get the time worked for the day, using the chart I already gave, I want excel to convert it to decimal format. So in my first example it should convert it to 8.3 hours and the second example it should convert it to 8.8 hours. It's true that if I work 8 hours and 55 minutes then it should give me 9.0 hours. "Rick Rothstein" wrote: I sort of read of that quickly and took it to mean the OP knew how to form a time value (from the hours and minutes) but didn't know how to apply his chart to it. But in re-reading it more carefully, I'm now thinking you are right in your interpretation. Assuming you are, here is a formula using my ROUNDUP approach (obviously, very similar in approach to your CEILING formula)... =HOUR(A1)+ROUNDUP(MINUTE(A1)/60,1) -- Rick (MVP - Excel) "T. Valko" wrote in message ... Hmmm... have excel convert from time format I read that as entering a time. 8:12 On the conversion table, if a time is =55 minutes it gets rounded up to the next whole hour so a time with 0 minutes should remain at 0 minutes/tenths. At least, that's my take! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... I'm not entirely convinced the OP has the hours and minutes bundled up into a time value (rather, I'm thinking the hours are in one cell and the minutes in another). The reason I suspect this is the chart the OP says he uses starts a 1 minute and ends at 60 minutes and, of course, no time value would have 60 minutes in it (it would have the 0 minutes missing from the chart). -- Rick (MVP - Excel) "T. Valko" wrote in message ... =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) That returns incorrect results. The OP's conversion table starts at 1 minute so does that mean a time like 11:00 PM should return 23.0 ? With the above formula: 11:00 PM = 23.1 9:54 AM = 10.0 9:42 PM = 21.8 4:12 AM = 4.3 And here's a weird one: 1:06 PM = 1.1 11:06 PM = 23.2 Both have the same minute yet return a different decimal. This seems to work: =INT(A1*24)+CEILING(MINUTE(A1)/60,0.1) Format as General or Number 0 minutes doesn't get rounded. 11:00 PM = 23.0 -- Biff Microsoft Excel MVP "Glenn" wrote in message ... dwhapp wrote: I use excel spreadsheet for entering my time worked. I have it currently set to add up hours worked, etc. Is there a formula I can enter to have excel convert from time format to tenths. For example, if I work 8 hours and 12 minutes, I want it to convert it to 8.2 hours. We use the conversion chart below. Any suggestions? 1-6 minutes = .1 7-12 minutes = .2 13-18 = .3 19-24 = .4 25-30 = .5 31-36 = .6 37-42 = .7 43-48 = .8 49-54 = .9 55-60 = 1.0 =INT(A1/TIME(1,,))+ROUNDUP(((MOD(A1,TIME(1,,)))*24),1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting time to decimal | Excel Worksheet Functions | |||
converting a decimal to time | Excel Worksheet Functions | |||
Converting decimal to time | Excel Discussion (Misc queries) | |||
Converting Decimal to Time | Excel Discussion (Misc queries) | |||
Converting from time format to decimal and figuring the difference | Excel Discussion (Misc queries) |