From and To Times

[Tdub]

Row A is start time
Row B is end time
Row C is total time 00:00 or *24 = 0.0
Row D is Day time 08:00 to 20:00
Row E is Night time 20:00 to 08:00

How to write saying calculate if A and B time is in between (8am and 8pm = day) and (8pm and 8am = night). Is it a sumif?

So
D = sumif whatever A and B have that's from "08:00 to 20:00"
E = sumif whatever A and B have that's from "20:00 to 08:00"

After that it's being *24 to get it into a decimal form to get time 00:00 to 0.0

Thanks all!

Edit.
Well I guess more sense would be 08:00 - 20:00 is day and 20:01 - 07:59 is night.

[Richard Buttrey]

Hi,

Perhaps
C1:

Please Login or Register  to view this content.

D1:

Please Login or Register  to view this content.

both copied down

[Tdub]

Cool, let me try and play around with it. Weird that the formulas would be different.?. For rows D and E?

I've got C already as =B-$A to get the total time. Then it's just *24. I forgot why I put the dollar sign at A but it needed to be there for some reason.

[Tdub]

I like the looks of =(B1-A1)+IF(B1<A1,24,0)-D1

But I don't know if I still understand the formula.

Lets say

A is 19:30 and B is 20:05.

So C would be 00:35 or 0.6

How would I write
D to get 0.5
E to get 0.1

[Tdub]

Doesn't work correctly when changing times. It's got to take in account A and B.
And the window needs to be Max(20,0,0) Min(8,0,0) for A
And Max(7,59,0) Min(20,1,0) for B
Something like that?

I've uploaded a sheet:

[Tdub]

Okay. There's a =round formula that does not work correctly because it will calculate into a negative answer where the least should be 0.0. A negative number will throw off other calculations.

These seem to work correctly:
=(MIN(B2,TIME(20,0,0))-MIN(A2,TIME(20,0,0)))*24 for day
=(MAX(B2,TIME(20,0,0))-MAX(A2,TIME(20,0,0)))*24 for night

Now the only issue to run into is lets say the time is 22:00 - 01:00 the next day.
How to tell it to calculate realizing it's into the morning of the next day without calculating backwards. Or formatting the cells still to look like HH:MM

So it'd be 03:00 hours total *24 = 0.3
Day = 0.0
Night = 3.0

?? Thanks for the help