Hi All

How can I categorize time as Day or Night?
For example, I have accidents data with the time of occurring of the accident.
If I set that 7:00 PM - 6:00 AM as NIGHT and 6:01 AM - 6:59 PM as DAY, then how can I categorize that the accident occurred during day or night.

The sample of data is as such:


Accident Time Day/Night
0:00 Night
0:35
0:50
0:14
0:02
0:30
1:52
1:28
1:01
1:50
1:30
1:25
1:20
1:39
2:20
2:30
2:17
2:30

I have a large set of data (about 800 entries). Please help me with the formula to use to organize the data.

Thanking you in anticipation.

Waqas

=if(and(a1>(6/24),a1<(19/24)),"day","night")

I'm sorry I've tried the "if" function already and it is not working.

Could you tell us what is not working?
Are they real times or text looking like time ( left aligned if no forced alignment) ?
If so try TIMEVALUE(A1) instead of A1

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The problem is that it is giving the same output for every entry and doesn't return the value as "DAY" as specified by the conditions.

Yes, accident time is actual time (cell format is set to time).

.
Assuming your time data begins in F2 :

Sorted!

Thank you for the help!

You are welcome.

It is not because the cell format is set to time that the contents are real time.
Read this about how Excel sees Dates and times

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post