Time

[Tom S]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

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.

 

[Tom S]

Thanks, I'll give it a try

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.

 

[Tom S]

Thanks, it looks like it is working using the Ceiling option. Thanks for
your help