MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Guru's, help please.
I am trying to understand if a start and finish time includes a working period.
The context is that trucks arrive at a distribution centre on a specific date and time and leave on a specific date and time. This is evidenced on a log, which has arrival and departure columns in format dd/mm/yyyy hh:mm.
However, the trucks can only be worked between the hours of 0700hrs and 1600hrs.
Simply, what I am trying to do is understand whether the trucks arrived within that period.
Any thoughts?
Thanks!
Split the arrival time into a date & a time column and then:
=AND(A1>TIME(7,0,0),A1<TIME(16,0,0))
=IF(AND(HOUR(A1)>=7,HOUR(A1)<16),"YES","NO")
where A1 is the cell containing your datetime
^^
better than mine..
Thanks both for your assistance.
Unfortunately, for some reason, the formula is not returning the expected result
Cell A1 contains:
02/01/2020 12:16:00
In custom format dd/mm/yyyy hh:mm
But, when I input the formula from ebygomm in cell B1, it is returning NO.
Any thoughts?
The formula returns YES for me with that date
If you use =HOUR(A1) do you get 12?
ebygomm's way worked for me although there are issues copying the text from the forum. I had to retype "Yes","no" for it to work. I guess there's some hidden formatting that Excel doesn't like.
EDIT: The code tags resolve that particular issue for me. Try copying / pasting from below.
=IF(AND(HOUR(A1)>=7,HOUR(A1)<16),"Yes","No")
Yes, it is returning 12, but formula still returning NO.
As a workaround, I've created the start hour in another column using =HOUR(A1) and then =IF(AND(AJ3>=7, AJ3<=16), "Yes", "No").
Weird....
I'll drop you a PM....
My fault....I incorrectly referenced the cell within the formula. The data was in cell I3 and I referenced I1...what a 'kin Muppet!
Thanks all for your patience and assistance.
IT WORKS 🙂
Only thing to bear in mind as if the time is 16:00:00 exactly this will count as outside the time period, which may not be what you want.
ebygomm’s way worked for me although there are issues copying the text from the forum. I had to retype “Yes”,”no” for it to work. I guess there’s some hidden formatting that Excel doesn’t like.
My money would be on the quote marks being some sort of Unicode chicanery rather than ASCII #34.