Calculate time outside of specified ranges

[delirium]

I am staring at this excel spreadsheet blankly ... I am at a loss as to how to accomplish the detailed analasys but simple math this spreadsheet reequires.

The data consists of three columns pasted from a report that is exported as a .csv:

B
Code
C
Start Time
D
End Time

The following analyses must be made on each row:
1. determine if the start time falls within the scheduled shift

Please Login or Register  to view this content.

2. determine if the end time falls within the scheduled shift

Please Login or Register  to view this content.

3. establish how much of each time segment falls within an approved time frame (i.e.: scheduled breaks, lunches, and other approved time frames)

**Step 3 is where I'm stumped because there are so many evaluations that are required. For instance. If the overlaps a scheduled break or lunch then that time is exempted and all I need is the duration outside of the exempted time, and I was planning on making a check box or drop down to manually remove other times**

The spreadsheet has been attached for reference ...

I think I'm way out of my depth and would appreciate any help anyone could give me.

Thanks so much.

[darkyam]

No attachment...

[delirium]

hmm ... upload of file failed ...

[shg]

Try zipping it.

[delirium]

Was too big for some unknown reason ... fixed ...

[shg]

Explain again what you want in col M?

[delirium]

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]

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

[daddylonglegs]

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]

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]

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.