Late/OnTime Deliveries - Formula Required

[KatieA]

Good Morning, does anybody know a formula that they can provide myself with to do the following.

I want to show on time, late, early deliveries and for explanation to appear in column g of the spreadsheet.

I have attached a spreadsheet as a clear idea what I am looking for. I know it will prob be an if statement, but this is one function I have not got my head around yet!! I bet when i see the formula i will understand ifs a lot more.

Please please help I need this to help at work other my boss will kill me

[WideBoyDixon]

OK. This was a little tricker because some cells (A3, C3, E3) contained a date AND a time (A3 = 25/11/2021 15:00) so I had to use INT and MOD to factor those out. Formula for G2 is then:

Please Login or Register  to view this content.

You can copy that down.

WBD

[etaf]

Is this a sample data and will the cells sometimes have time and date in - as some did in your sample as posted above, so you can use that formula

Also are there any other conditions or just Early , Ontime, Late
Not interested if very very late ?

I have corrected and assumed they will be JUST a date and JUST a time

Also showed each calculation so you can see how made up
=IF(AND(E2+F2<=C2+D2,E2+F2>=A2+B2),"ON TIME", IF(E2+F2<A2+B2,"Early", IF(E2+F2>C2+D2,"late","Error")))

Nested IF
if you need explanation on IF or nested IF - post back and we can try and explain further

see columns I , J , K & l

[KatieA]

Hello WBD, thank you ever so much for solving that puzzle for me. I wanted to see how a forumla would be written if the date and time was situated in the same column. I have now used that in my code and it works fine I am still learning from what you have done.

[KatieA]

ETAF - thank you for sending over an example of how the formula is broken down, I will look at this as soon as I can, and if I need any more help I will let you know. This is why I love the forum. There are kind people like yourself and WBD that help solve excel to make lives simpler.

[etaf]

you are welcome

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.