Count hours (time) between four time criteria

I attempted to understand the formula, but I aint that good if statements are my limits haha

however, using that i get a return value of c1=72 and d1=10

A1=4:00
B1=17:00

C1 should = 3
D1 should = 12

I assume then this cannot be achieved!

so these formula are the same but different

to get decimal hours you commented on *24

i get ######### when i do that, sorry, im new at this stuff, so i apologise in advance.

Boy oh boy, "alot to learn you have young one, alot to learn", is what yoda would be saying to me right now!

Now finally, i hope,

If there are no hours in the fields, the formula locations are reporting a '-6'

why would that be, and how to i get rid of the little thing.

I don't think Donkeyote's formula will work for all scenarios, e.g. try 20:00 to 06:00.

This formula should cope with any shift less than 24 hours long

for hours between 06:00 and 16:00

=((A1>B1)*MEDIAN(0,B1-1/4,5/12)+MAX(0,MIN(2/3,B1+(A1>B1))-MAX(1/4,A1)))*24

for all other hours

=24*MOD(B1-A1,1)-C1

Note: in the first formula 1/4 represents 06:00, 2/3 represents 16:00 and 5/12 is the difference between those 2 times (10 hours)

Well, you have now all successfully stumped me (confused me).

Can someone explain this one to me?

The other issue is that I will eventually need to have the time (either 6:00 or 16:00 vary depending on user input, so all of the fractions would appear not to cope at all with that situation.

DonkeyOte... What if there were three time interval variables, like for shift work.

THat is to say, hours between say 07:00 to 15:00, 15:00 to 23:00, and 23:00 to 07:00, is it a case of adding another min/max?

Originally Posted by adhide

Well, you have now all successfully stumped me (confused me).

Can someone explain this one to me?

Hello adhide

My suggestion is a little more complex than Donkeyote's, but I believe it will give the correct results in all circumstances.

You might be able to use a less complex formula but that would depend on the possible start and end times you have. My suggestion will cope with all possible start and end times, assuming shifts are always less than 24 hours. If your shifts are more restricted then please can you define the limitations, I note you suggested that one shift might be 05:00 to 02:00, i.e. 21 hours?

If you want to make the formula more flexible then you can use cell references for the start and end times, e.g. if J2 contains the start time of the range to count, e.g. 06:00 and K2 the end time, i.e. 16:00 then the formula becomes

=((A2>B2)*MEDIAN(0,B2-J$2,K$2-J$2)+MAX(0,MIN(K$2,B2+(A2>B2))-MAX(J$2,A2)))*24

Now you can change the values in J2 and K2 without changing the formula itself......

Note: K2 must be greater than J2

See attached examples, I also included a sheet where hours are calculated between 07:00 and 15:00, 15:00 and 23:00 and 23:00 t0 07:00. Again you can just change these time bands by changing the values in J2:K3. Again the start time must be earlier than the end time (the undefined range must always be the range that passes through midnight)

If A2 and B2 are blank the formulas return zero

I used =RAND() formula to generate random times either on the hour or half hour, just for demonstration purposes. Press F9 to make times re-calculate or enter your own times.....