Date and time comparison

Hi,

I have a problem. I have a date time data like Dec/09/14 10:41:00. From this data I need to solve the following problem.

We have a cut off time to do work i.e., work received after 2 PM of previous day and before 2 PM of current day will be processed on current day. Now suppose I have received some work at Sep/28/14 14:41:00, so this will be processed in Sep/29/2015 and if work received at Sep/28/14 13:41:00 this should be processed on Sep/28/2015. I have a column where we enter processed date like Sep-28-2015, Sep-29-2015 and this is the date when a particular work has processed. Now from this data I need to compare that how much I have processed on time and on how much work I missed the deadline.

Can anybody help me please.

Hi Glenn

Above formula works for me, but I have the following issue with it:

1. Excluding of weekends and holidays as work received on Friday after 2 PM will be processed on Monday. So as per above formula, work should be processed on Saturday otherwise it will be late but this is not the scenario. Work will be processed on Monday and same with holidays.

2. The work received after 2 PM shouldn't be processed on the same day. So formula should throw an error.

It will be very helpful for me if you can help me with the above issues.

Originally Posted by Glenn Kennedy

OK. Try this, then:

Formula:

Please Login or Register  to view this content.

where M2:M6 contain a list of holidays. see the sheet....

Hi Glenn,

Thank you for your help. You solved my problem. I just have a question. I am not able to understand the logic of networkdays function means you have taken the networks days as >,= 2. What is the logic behind this?