Help with time conversions

[WPP]

I have a small problem that I can't seem to solve if anyone can help
it would be appreciated.

Time cells are h:mm AM/PM decimal cells are 0.00

B14 = AM time(eg: 7:15 AM) C14 = AM time later than B14 (eg: 9:15 AM)

D14 = PM time (eg: 2:30 PM) E14 = PM time later than D14 (eg: 7:15 PM)

I need to make B15 tell me that if B14 is earlier than 8am the
difference between 8am and the time entered and give me the answer in
a decimal number. In this case the answer is 0.75

I then need to make B16 tell me the difference between 8am and the
time in C14 in a decimal number. In this case the answer is 1.25.

I also need D15 tell me that if E14 is after 6pm the number of hours
from 6pm to the time entered in decimal number. In this case the
answer is 1.25

And likewise I need cell D16 to give me the time difference between
cell D14 and E14 up to 6pm. So in this case the answer is 3.5.

If there is any excel guru out there that can figure that out please
feel free to solve away.

 

[JBeaucaire]

Slight correction:

7:15 AM 9:15 AM 2:30 PM 7:15 PM
--0.75----1.25----3.5-----1.25


B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24)
C15 =(C14-TIME(8,0,0))*24
D15 =IF(E14>TIME(18,0,0),(TIME(18,0,0)-D14)*24)
E15 =(E14-TIME(18,0,0))*24

 

[WPP]

Slight correction:

7:15 AM   9:15 AM   2:30 PM   7:15 PM
--0.75----1.25----3.5-----1.25

B15     =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24)
C15     =(C14-TIME(8,0,0))*24
D15     =IF(E14>TIME(18,0,0),(TIME(18,0,0)-D14)*24)
E15     =(E14-TIME(18,0,0))*24

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.







- Show quoted text -

Thankyou you are an angel (oops "rocket scientist").

However the calculations in cell E15 gets all mucked up if the time
goes past 6pm it shows the figure as a negative rather than a
positive.
I can correct this by removing the "-" sign from in front of the *24
but then when the time is less than 6pm I have to put the "-" sign
back???

What you have given me I can work with though and no doubt some
playing around will get me there.

Thankyou thankyou thankyou

 

[JBeaucaire]

You may have missed my followup formulas, but here's another set that you may
like better. It takes into account the fact that the 1st time may be after
8am, in which case a separate sum isn't needed, and the 4th time may be
before 6pm, in which case a 4th sum isn't needed. In both instances you only
need one time interval for the pairs, right?

Like so:

B15 =IF(B14<TIME(8,0,0),(TIME(8,0,0)-B14)*24,"")
C15 =IF(B14>=TIME(8,0,0),(C14-B14)*24,(C14-TIME(8,0,0))*24)
D15 =IF(E14>TIME(18,0,0),(TIME(18,0,0)-D14)*24,"")
E15 =IF(E14<=TIME(18,0,0),(E14-D14)*24,(E14-TIME(18,0,0))*24)

 

[WPP]

Thankyou thankyou thankyou Thankyou thankyou thankyou
Thankyou thankyou thankyou