Calculate time outside of specified ranges

**shg** · 12-12-2009, 07:13 PM

Explain again what you want in col M?

**delirium** · 12-12-2009, 08:02 PM

I want to modify column J so that it only tallies the duration OUTSIDE of the excepted times in rows F5 / F6, H5 / H6 and J5 / J6 ... or discounts the duration entirely if a checkbox or drop down is selected.

ooh ... missed another crucial part ... the only time that counts is the time between B5 and B6 ... for instance if the shift is 8 am to 5 pm and there is a PROJ from 4:45 to 5:05 ... then the time is REALLY 4:45 to 5:00. The way I have it set up both the start time AND the stop time would have to be within the shift in order to count. That's wrong.

Geez I'm even confusing myself at this point.

**shg** · 12-12-2009, 08:25 PM

Still not sure what you're trying to do. Maybe this.

**daddylonglegs** · 12-12-2009, 08:34 PM

To calculate how much of the time falls within the start and end times in C5/C6 but not within the break times then you could use this formula in K11 copied down

=MAX(0,MIN(I11,C$6)-MAX(G11,C$5))-MAX(0,MIN(I11,G$6)-MAX(G11,G$5))-MAX(0,MIN(I11,I$6)-MAX(G11,I$5))-MAX(0,MIN(I11,K$6)-MAX(G11,K$5))

Note: your times include seconds so to display full accuracy you need to display as h:mm:ss

The formula only works assuming all times are on the same day....

**delirium** · 12-12-2009, 09:26 PM

That's great daddylonglegs! Thanks ... It seems to work miraculously!!! I think I love you ... and before you get all wound up ... I say that to everyone on this forum. i think you might have just saved my skin. and it does seem to be working for the breaks and lunch period ... did i misunderstand you when you said it wouldn't?

** n/m I am brayne ded ... I re-read your post and I understand now. **

I knew it had something to do with MAX / MIN but ... I just couldn't think it through.

**gpickering** · 10-31-2012, 12:16 PM

I am calculating times with in specified ranges. The formulas above work great, but I have several that span midnight. i.e. Start Time is 16:00 and the Stop Time is 02:00. I need it to calculate as follows: 1 hour in the coulumn of 15:00-17:00. 2 hours in the 17:00-19:00 column. 5 hours in the 19:00-24:00 column, and 2 hours in the 24:00-7:00 column.