Excel 2007 : working with Day and Time and calculating

**Kbass6319** · 05-09-2010, 11:23 AM

I need help with formula to calculate time only if the event occurs on certain days (Monday through Friday) and only if between 1:00PM and 7:00PM. Currently I have to go through all the data and do this manually and hope someone can help automated this process. I have attached a sample file with data dowloaded from loggers. The "On-Peak Hours" is the column I need to automate, everything else is calculating properly.

I use lighting loggers to record when lights come on/off so here's what I am hoping to do: If the event is recorded between 1:00PM to 7:00PM, Monday - Friday, I want subtract the ON time from the OFF time (Status Column). If it happens any other time, return 0. Often the lights will come on after 1:00PM, but not go off until after 7:00, but I must limit the total to 7:00PM.

I know there is a number of things to consider, but can this be done? Any help is greatly appreciated.

Thanks,

**royUK** · 05-09-2010, 12:12 PM

Try this in D22

=IF(WEEKDAY(A21,2)<6,IF((AND(HOUR(A21)>13,HOUR(A22)<19)),A22-A21),"")

**Kbass6319** · 05-09-2010, 04:38 PM

Thank you RoyUK! That is very close!! On the days when it logs past 7:00PM, is it possible to only calculate the time up to 7 and exclude the remaining?

I really appreciate the help!!

Keith