IF and AND with Dates and Times

[Francis85]

Hello,

I work for a Transport company. We send notification to our customers as to when we will be delivering with a given window of 2.5 hours.

Our planned delivery date/time is (G3) 05/05/15 08:19
Our start window for this is (H3) 05/05/15 07:49
Our end window for this is (I3) 05/05/15 10:19
We actually arrived at (F3) 05/05/15 09:25

I would like a formula to show in cell (J3) 'IN TIME' if it is within this window.

I was thinking it was =IF(F3>=H3 and <=I3,"In Time","Late"), doesn't like it though. I've played with a few variations, but I cant think of another way round.

 

[gaz_chops]

Try

=IF(AND(F3>=H3,F3<=I3),"In Time","Late")

 

[Francis85]

Done it!!

I used; =IF(AND(F3>=H3,F3<=I3),"In Time","Late")

...and it is working. Thought I'd post in case anyone didn't have an answer and also would use this function.

 

[Francis85]

Thanks Gaz_chops, I must have just done it as you posted. I persisted a little longer. Very useful, thank you

 

[gaz_chops]

You're welcome, glad you got there.

 

[Francis85]

Yes, thank you. I'm now working on the results given.

If 'Late' then show how many HH:MM late compared to the 'Planned Date/Time'. Using; =IF(J3="Late",TEXT(F3-I3,"h:mm"))

 

[gaz_chops]

You shouldn't need the text part, try this and format cell as mm:ss, add what to do if not "Late" - ,"" at the end will return a blank.

=IF(J3="Late",(F3-I3),"")

 

[Francis85]

Okay sure, I'll try that.

I'm now having a problem with some of them.

Actual Dely Planned Dely Start Window End Window Difference Late by HH:MM
05/05/2015 12:37 05/05/2015 13:08 05/05/2015 12:38 05/05/2015 15:08 Late #VALUE!

The difference shouldn't show 'Late' as it is 1min early; formula in this cell is; =IF(AND(F381>=H381,F381<=I381),"In Time","Late")

 

[gaz_chops]

Because it was early! Realise you should should only check if it is greater than end window! So

=IF(F3<=I3,"In Time","Late")

 

[Francis85]

Brilliant! Thank you, I'm too concerned with being 'within' the window didn't think about that. This will work for me, you're a star