Formula to work out day/night based on start and end hours

Good Morning All,
I need a little help with some data. I have a spread-sheet with over 10,000 rows of data. The attached file shows the headings and type of data in the cells. I'm struggling with a formula for column G (day/night). What I need to do is populate this column with 'night' but only where:

The start date and end date is the same and the issue occurred after 9pm (using start time column)The end time is equal to 5am or less but again where the start and end date is the same (based on end time)The difference between the start and end date is one day and the issue occurred at or after 9pm on the start date but at or before 5am on the end date.

I have no idea if this makes any sense but please let me know if further clarification is needed!

Once that is done, I then need to work out what took place on a Friday, or Friday into Saturday, Saturday or Saturday into Sunday etc.
Any, and I mean any help would be appreciated.
Thank you all in advance!
A Frustrated Excel User


Attachment 489841

Thank you so much for this. It works and I'm one happy bunny Really appreciate your help/

Thank you so much!

Ahhhh, yes, I didn't want to include too much data on the attached file. It works for data included but I will hold fire for now. Thanks for spotting this!

You're right...it doesn't work. Just tried it where the times are between 1am and 2am.

You cannot have an end time after midnight on the same day!!

If any shift starts after 9 pm, irrespective of end time, is it a "night" shift? so only test "Start Time"

Could a "night" shift finish as 06:00 ? If so, how is the 05:00 - 06:00 classified?

Hi John and thanks for your reply. Yes, it should be 9pm to 23.59 really and then midnight to 5am, all of which would be classified as night. 5am to 6am would not be classified as night in this instance. We are really looking at anything between 9pm and 5am really to be classified as night.

Try

=IF(ISNUMBER(C2),IF(OR(C2>TIME(21,0,0),AND(C2>=TIME(0,0,0),C2<=TIME(5,0,0))),"Night",""),"")

NOTE: row 18 appears "odd" as the dates are 2 days apart .

Hi John,
Yes, some of my data includes dates that are over 2 days old. Thank you for the formula which I will try asap!

Ok, I don't think I have explained my issue well enough as none of these formulas are not working
Let me give this another go:
If F2 = 1 and the start time is greater than or equal 21:00 and the end time is less than or equal to 05:00 hours, populate as night OR if F2 is equal to 0 and the start time is greater than or equal to 21:00 and the end time is less than or equal to 23.59 , then populate as night OR if F2 =0 and the start time is greater than or equal to 00:00 and the end time is less than or equal to 05:00, then populate as night.

Hi Ash and thank you for replying. I've tried this and it doesn't work It should say 'night' where the start date is 02/10/16 and end date is 03/10/16 and start time is 01:00 and end time is 02:00 but it does not populate accordingly. Thank you for trying.

I completely understand and will give it some rigorous testing. Again, thank you all! I may be back yet...

Me again....told you I might be back. The formula works with the majority of dates/times but when I had the start date as 01/01/16, end date as 02/01/16, start time as 21:00 and end time as 09:00, it populated it as night when really, it should have been blank. You did warn me...

On the plus side, this new one posted seems to be working fine, although I will test it further next week and may be back in touch My weekend starts here so again, I can't thank you enough for your time and patience. Maybe speak next week!

Originally Posted by pjwhitfield

Its been an interesting one, thanks for posting it.

Ash, I think your version suffers from the same thing as my previous one ie if EITHER Start or Finish Time is within the range then it shows night but BOTH must be.

No, thank you for taking the time to look into it for me.

Originally Posted by Ash_Maverick

Can you please give few more examples in this new attachment. Tell what should be NIGHT and what shouldn't be.

Hi Ash,
Thank you for your help too. I can't see my file anymore as I'm no longer in the office but night should be anything where:
The start and end dates are the same and the time falls between 21:00 and 23.59
Where the the start and end dates are the same and where the time is between midnight and 05:00
And finally, where the start and end dates are only one day apart and where the time falls between 21:00 and 05:00.
The attachment I posted probably wasn't the best example really as those dates and time are all over the place. I think the formula posted by pjwhitfield will do the trick. Thank you again.