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