Calculate time outside of specified ranges

**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.