Excel 2007 : working with Day and Time and calculating

[Kbass6319]

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,