Bonus Points!!!

[Username]

No one could answer this yesterday, so it must be really hard. Only a true Excel genuis can help me...

I have a spreadsheet with one row per day.
Every day I record the time we start and stop painting, and when we change air filters. I need an elegant way to calculate how many production hours passed since the last filter change. We don't paint non-stop, the filter change time varies, and some days one or the other doesn't occur.

Date Start Stop Filter changed
3/08/02 6:00 AM 2:30 PM 12:50 PM
3/09/02 6:00 AM 12:30 PM None
3/10/02 No Production
3/11/02 6:00 AM 2:00 AM 6:00 AM
3/12/02 6:00 AM 2:00 AM 1:00 AM
3/13/02 6:00 AM 2:00 AM 4:00 PM
3/14/02 6:00 AM 2:00 AM None
3/15/02 6:00 AM 2:30 PM 11:00 AM
3/16/02 No Production
3/16/02 No Production
3/17/02 6:00 AM 2:00 AM 1:00 AM

Example results would be
3/13 filter changed after 9 hours
3/15 filter changed after 15 hours

I am using a nested IF/THEN, but there is not enough room to jump over more than 6 non-production days. I need to keep all the days, because I have other sheets linked by OFFSET formulas.

Thanks
John H.

 

[Dave Patton]

Is your example correct.
Are the times correct.
Start at 6: end at 2: is 20 hours?

 

[Mark W.]

I couldn't make sense of your data. It appears to be space delimited, but "No Production" contains a space. Couldn't figure out which column contained this value. If your values include spaces you should choose another delimiter such as a comma.

 

[Username]

It looked great when I set it up, must be a variable sized font. I'll try again, delimited by ";"

We have two shifts. First shift is 5 days, 8 hrs/day, 6:00 AM to 2:30 PM. Second shift is 4 days, 10 hrs/day, 3:30 PM to 2:00 AM. There are two 1/2 hour lunch breaks (11:30-12:AM and 7:00-7:30 PM) and one hour between shifts (2:30-3:30 PM), but I thought that would really be a killer to add. Sometimes we work one shift on Saturdays, sometimes we shut down for a week. Holiday schedules get weird.

Date Start Stop Filter changed
3/08/02; 6:00 AM; 2:30 PM; 12:50 PM
3/09/02; 6:00 AM; 12:30 PM; None
3/10/02;;; No Production
3/11/02; 6:00 AM; 2:00 AM; 6:00 AM
3/12/02; 6:00 AM; 2:00 AM; 1:00 AM
3/13/02; 6:00 AM; 2:00 AM; 4:00 PM
3/14/02; 6:00 AM; 2:00 AM; None
3/15/02; 6:00 AM; 2:30 PM; 11:00 AM
3/16/02;;; No Production
3/16/02;;; No Production
3/17/02; 6:00 AM; 2:00 AM; 1:00 AM

 

[Mark W.]

Suppose your data (as shown above) is in A1:D12. The formula to calculate cumulative "Post Maintenance Production Hrs" could be entered into E2 as...

=IF(ISNUMBER(D2),C2-D2+(C2<D2)-(C2="2:00"+0)*(A2+"2:30 P">A2+(D2<0.25)+D2)/24,N(E1)+C2-B2+(C2<B2))-(D2="None")*(C2="2:00"+0)*(C2<0.5)/24

...and copied down to E12. Format column E as...

[h]:mm

This is such an elegantly designed sample data set I'm guessing it's literally a "text book" example; therefore, I'll let you do some of the [home] work and figure out how to factor in lunch hours. At least this should get you started!

Some documentation...

1. ISNUMBER(D2) is TRUE if filter was changed
2. C2-D2+(C2<D2) equals "wall clock" hrs since filter change
3. -(C2="2:00"+0)*(A2+"2:30 P">A2+(D2<0.25)+D3)/24 deducts 1 hour if filter was changed before 1 hour inter-shift "break"
4. N(E1)+C2-B2+(C2<B2)) equals cumulative "wall clock" hrs since filter was last changed
5. -(D2="None")*(C2="2:00"+0)*(C2<0.5)/24 deducts 1 hour if inter-shift "break" occurred on a day when a filter wasn't changed
This message was edited by Mark W. on 2002-03-22 06:33

 

[Dave Patton]

consider in maint col

=IF(D7>0,(D7-B7+(B7>D7))+G6,"")

Carry fwd column G

=IF(D7>0,(C7-D7+(D7>C7)),(C7-B7+(B7>C7))+G6)

ensure you use custom format [h:]mm