Help with calculating Early/On Time/Late from Set Time Windows

**warbopaul** · 07-20-2012, 03:58 PM

Hi there. I am new to the forum and am looking for some help. Normally I can solve most things in Excel but this has me stumped.

I am in need of creating a spreadsheet that will report whether an imported delivery time is either Early/Late or On Time based on a fixed Time Window. I have the following result so far:

Earliest Latest Actual Delivery Status
20:00 22:00 23:00 Late Calculation works
23:30 1:00 23:45 Late Doesn't Work - Should be On Time
0:15 1:45 0:10 Early Calculation works
4:00 7:00 5:00 On Time Calculation works

The calculation I have come up with so far is:

=IF(K7>(greater than)L$1,IF(AND(I7<(less than)K7,J7<(less than) K7),"Late",IF(AND(I7>(greater than)K7,J7>(greater thna)K7),"Early",IF(AND(I7<(less than)M1,M1>(greather than)K7),"On Time","On Time"))))

In the calculation, L$1 is defined as 00:00:00 and M1 is 23:59:00. K7 is Actual Delivery Time, I7 is Earliest Delivery Time and J7 is Latest Delivery Time.

The problem comes when the Earliest and Latest Delivery time essentially go through midnight.

I have tried many different ways to get the calculation to work but with no joy.

I am using Excel 2003 on Windows XP.

Any help with resolving the problem would be gratefully appreciated.

Thanks.

**ASmartBomber** · 07-20-2012, 04:26 PM

I would suggest creating a duration calculation to define the On Time time period and then compare this result to the duration between the actual delivery time and the start time of the On Time time period.

Duration =IF(OR(B29="",C29=""),"",(C29-B29+(C29<B29)))

where B29 is the start time and C29 is the end time of the duration to be calculated. Make sure that the cells are in the correct time format.

**Ishtiyaq** · 07-20-2012, 04:34 PM

Hi,

You would have to calculate net hours for your formula to work. I've attached a sample excel sheet that can make your current formula work.

Warm regards
Ishtiyaq

**warbopaul** · 07-20-2012, 05:20 PM

Hi Ishtiyaq,

Thanks for the help but have had a play about with the input times (this being delivery time which will change, earliest and latest will be constant) and things have started going wrong.

Can you have a look at the attached and see if you can offer anymore advice. It's really appreciated.

Thanks.

**Ishtiyaq** · 07-20-2012, 11:36 PM

Hi Warbopaul,

Sorry about the error. I've changed the formula and format, I did play with numbers and it worked for me. I'm not sure why its not working with your formula ideally it should but I'm running out of time so I can't check that. Please review the sheet and let me know whether it serves the purpose or not. If not I'll see what best I can do.

Have a great day.

Warm regards
Ishtiyaq

**Usman Qasim** · 05-19-2023, 11:56 AM

I need Help
I am Using This Formula For Calculating Early Time Of Staff
=IF(F4<=E4,E4-F4,"On Time")

This Should be wrong because when the employee is on off this should be show us his schedule out time to Early time

**FDibbins** · 05-20-2023, 01:07 AM

Originally Posted by Usman Qasim

I need Help
I am Using This Formula For Calculating Early Time Of Staff
=IF(F4<=E4,E4-F4,"On Time")

This Should be wrong because when the employee is on off this should be show us his schedule out time to Early time

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread