Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time
I am trying to get a formula that will give me time in tenths. For example;
20:30 to 09:00, should equal 12.5 I want the formula to use every 6 minutes as a .1 I am using =MOD(C9-C8,1)*24 now. It changes at evey ten minutes to a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2 Every 6 minutes is a Point not every ten minutes. Can anyone help me with this?? I will also be using times from the same day, like 08:00 to 20:08 comes up as 12.1 and it should be 12.2 Thanks Tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time
a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2
Why? 20:30 to 09:38 is 13:08 which is 2 minutes after 13:06 and four minutes before 13:12 so it should be 13.1333333333 which is whxct I get from your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Tom S" wrote in message ... I am trying to get a formula that will give me time in tenths. For example; 20:30 to 09:00, should equal 12.5 I want the formula to use every 6 minutes as a .1 I am using =MOD(C9-C8,1)*24 now. It changes at evey ten minutes to a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2 Every 6 minutes is a Point not every ten minutes. Can anyone help me with this?? I will also be using times from the same day, like 08:00 to 20:08 comes up as 12.1 and it should be 12.2 Thanks Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time
By the sound of it, you may want to round *up*, rather than round to the
nearest. If so, try: =ROUNDUP(MOD(C9-C8,1)*24,1) or =CELING(MOD(C9-C8,1)*24,0.1) I think you've misinterpreted it when you say it changes every 10 minutes. Check your data again. -- David Biddulph "Tom S" wrote in message ... I am trying to get a formula that will give me time in tenths. For example; 20:30 to 09:00, should equal 12.5 I want the formula to use every 6 minutes as a .1 I am using =MOD(C9-C8,1)*24 now. It changes at evey ten minutes to a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2 Every 6 minutes is a Point not every ten minutes. Can anyone help me with this?? I will also be using times from the same day, like 08:00 to 20:08 comes up as 12.1 and it should be 12.2 Thanks Tom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time
Thanks, I'll give it a try
"David Biddulph" wrote: By the sound of it, you may want to round *up*, rather than round to the nearest. If so, try: =ROUNDUP(MOD(C9-C8,1)*24,1) or =CELING(MOD(C9-C8,1)*24,0.1) I think you've misinterpreted it when you say it changes every 10 minutes. Check your data again. -- David Biddulph "Tom S" wrote in message ... I am trying to get a formula that will give me time in tenths. For example; 20:30 to 09:00, should equal 12.5 I want the formula to use every 6 minutes as a .1 I am using =MOD(C9-C8,1)*24 now. It changes at evey ten minutes to a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2 Every 6 minutes is a Point not every ten minutes. Can anyone help me with this?? I will also be using times from the same day, like 08:00 to 20:08 comes up as 12.1 and it should be 12.2 Thanks Tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time
Thanks, it looks like it is working using the Ceiling option. Thanks for
your help "David Biddulph" wrote: By the sound of it, you may want to round *up*, rather than round to the nearest. If so, try: =ROUNDUP(MOD(C9-C8,1)*24,1) or =CELING(MOD(C9-C8,1)*24,0.1) I think you've misinterpreted it when you say it changes every 10 minutes. Check your data again. -- David Biddulph "Tom S" wrote in message ... I am trying to get a formula that will give me time in tenths. For example; 20:30 to 09:00, should equal 12.5 I want the formula to use every 6 minutes as a .1 I am using =MOD(C9-C8,1)*24 now. It changes at evey ten minutes to a .1, so like 20:30 to 09:38 comes up as 13.1 and it should be 13.2 Every 6 minutes is a Point not every ten minutes. Can anyone help me with this?? I will also be using times from the same day, like 08:00 to 20:08 comes up as 12.1 and it should be 12.2 Thanks Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |