Difference Between two dates that have time windows.

[KatieA]

Good Afternoon, I will admit now that I am a novice when it comes to date and time, and also if statements. I am trying to find a solution for the following online but I can not seem to see one about calculating date and time with time windows.

What I am trying to achieve is the following, if any one can help provide the solution or send me in the right direction at least. I have attached my example. I am still learning if statements, think it will sink in soon.

So if a delivery is late, I would like it to state in the column by how many hours and days. So in writting if the delivery date and time as a booking window was 25/11/2021 08:00 - 25/11/2021 08:00 and the date was stated as 26/11/2021 08:00, this would show as 24 hours overdue and 1 day late and so on.

I am unfamiliar with how excel would calculate a time window, and this is why I am seeking advice. I have only done the example as written in logical and not excel.

Thank you so much if any of you can help.

[KOKOSEK]

	A	B	C	D	E	F	G	H
1	Window Open	Window Open	Window Close	Window Close	Delivery	Delivery	Hours Over/Under	Days
2	25/11/2021	09:00	25/11/2021	09:00	26/11/2021	09:01	+ 24:01:00	1
3	25/11/2021	15:00	25/11/2021	19:00	25/11/2021	18:36	in window	in window
4	25/11/2021	00:00	25/11/2021	23:59	24/11/2021	00:00	- 24:00:00	1
5	25/11/2021	08:00	25/11/2021	08:00	25/11/2021	07:59	- 00:01:00	0
6	25/11/2021	08:00	25/11/2021	08:00	27/11/2021	08:02	+ 48:02:00	2

Check attached file as for some unknown reason I can't post formula (firewall blocked as suspicious code, even after wrap it into code tag).

p.s.

[KatieA]

I am sorry but on the spreadsheet it has come up as, what you have shown as the example is exactly what I need,but I do not understand what the formula is showing, never seen it like this before.

Please Login or Register  to view this content.

=@_xlfn.LET(_xlpm.DT,E2+F2,_xlpm.OpenW,A2+B2,_xlpm.CloseW,C2+D2,IFERROR(ROUND(IF(@_xlpm.DT<@_xlpm.OpenW,@_xlpm.OpenW-@_xlpm.DT,IF(@_xlpm.DT>@_xlpm.CloseW,@_xlpm.DT-@_xlpm.CloseW,"")),0),"in window"))

Please Login or Register  to view this content.

[KOKOSEK]

Your profile states that you are using Office 365 so I've used function LET.
If you got error it means that you are using different version of office.
Then:

	A	B	C	D	E	F	G	H
1	Window Open	Window Open	Window Close	Window Close	Delivery	Delivery	Hours Over/Under	Days
2	25/11/2021	09:00	25/11/2021	09:00	26/11/2021	09:01	=IF((E2+F2)<(A2+B2),"- "&TEXT((A2+B2)-(E2+F2),"[hh]:mm:ss"),IF((E2+F2)>(C2+D2),"+ "&TEXT((E2+F2)-(C2+D2),"[hh]:mm:ss"),"in window"))	=IFERROR(ROUND(IF((E2+F2)<(A2+B2),(A2+B2)-(E2+F2),IF((E2+F2)>(C2+D2),(E2+F2)-(C2+D2),"")),0),"in window")

[KatieA]

Kokosek, absolutely brilliant problem solver. I really appreciate the neatness of your example, to. It was so helpful for a rookie like me.

How bizarre I didnt even know that could happen. I use 365 at home, but now I have looked the one in properties, I am working with at work is office 16, however when I open and shut my document, it says it is 365....!!

I have only just started to work for this company, so will need to advise next time when asking for assistance from work, as I dont want to look like such a silly sausage!!!

[KOKOSEK]

Happy to help.
Thanks for rep.

[KatieA]

Kokosek, it is really appreciated, as I am under pressure to analyse 100 of deliveries, my excel is good, but I only use certain functions so I am trying to broaden my understanding. I am sorry to be a pain, is there any way in the formulas that if there wasnt any data for the actual delivery time, that it could show something like a message such as delivery overdue/warning/ or no EPOD available. If not no worries just thought I would ask.

[KOKOSEK]

Something like this is ok:

	A	B	C	D	E	F	G	H
1	Window Open	Window Open	Window Close	Window Close	Delivery	Delivery	Hours Over/Under	Days
2	25/11/2021	09:00	25/11/2021	09:00	26/11/2021	18:36	+ 33:36:00	1
3	25/11/2021	15:00	25/11/2021	19:00	25/11/2021	18:36	in window	in window
4	25/11/2021	00:00	25/11/2021	23:59	24/11/2021	00:00	- 24:00:00	1
5	25/11/2021	08:00	25/11/2021	08:00			no EPOD avail
6	25/11/2021	08:00	25/11/2021	08:00	27/11/2021	08:02	+ 48:02:00	2

[KatieA]

Kokosek, yes this is brilliant. Thank you so much

[KOKOSEK]

No worries. Happy to help.

[KatieA]

Hey I know this might seem a bit silly to come back and ask so late. However here is now my dilemma.

If a formula is present in the cell rather than it being blank for the actual delivery date/ time, which I want to use the if error on, it is returning value rather than putting the cell as blank or what ever I put it as.

Do you have any idea why or how to amend it.

[AliGW]

What does the formula return?

You could try this:

=IF(ISERROR(FORMULATEXT(E2)),IF(E2+F2=0,"no EPOD avail",IF((E2+F2)<(A2+B2),"- "&TEXT((A2+B2)-(E2+F2),"[hh]:mm:ss"),IF((E2+F2)>(C2+D2),"+ "&TEXT((E2+F2)-(C2+D2),"[hh]:mm:ss"),"in window"))),"")

[KatieA]

Ali when using the formula that was given previously it returns #value!

[KatieA]

This is the sample of data to show, where the error appears, i do not understand why it does it. If any one could advise then that would be great.
Would love to get rid of it, it shows #value! in column AE and this is what I am trying to get rid of, i want the cell to go blank/ or put N/A (NO POD) & not show an error. It is reading the data from column g&j.

I tried to make the sample as quick as possible and removed all the code that was in there, that isnt to relevant.

[AliGW]

The start of the formula creates the #VALUE! error because the cells do not contain number values:

IF((G2+J2)>0

Use Evaluate Formula on the Formulas ribbon to step through your formulae to see where they are falling over.

[KatieA]

I looked at that previously and I cant understand why it isnt working. Oh well.