Is there an excel function that I can use to give values based on what the time value is?-CodePudding

I'm looking for a function I can use that returns "Night Off Prime" if the time a programme was on was between 11pm - 5:59am, "Day Off Prime" if it was on between 6am - 5:59pm and "Prime" if it was on between 6pm - 10:59pm.

I've tried using the IFS function with the code being =IFS(OR(G3>=23,G3<6),"Night Off Prime", OR(G3>=6,G3<18),"Day Off Prime", OR(G3>=18,G3,23),"Prime")

The G column is just the hh format of the hour it was on, with the values formatted as Number.

I've also tried =IFS(OR(BK3>=23:00,BK3<06:00),"Night Off Prime", OR(BK3>=06:00,G3<18:00),"Day Off Prime", OR(BK3>=18:00,BK3,23:00),"Prime")

Here the BK column is the time the programme was on in the hh:mm time format

The main trouble I'm finding there is how to label time between two different times such as between 11pm - 5:59am instead of just past 11pm. I used the OR logical but that doesn't seem to have worked.

I've also tried a VLOOKUP function =VLOOKUP(BK2,$AC$899:$AD$901,2, TRUE)

Here the table array is

AC899 Night Off Prime AD899 23:00-05:59

AC900 Day Off Prime AD900 06:00-17:59

AC901 Prime AD901 18:00-22:59

CodePudding user response：

If you prefer the IFS you may want to adjust like this (should work in the similar way for hh:mm as well):

=IFS(G3<6;"Night Off Prime";G3<18;"Day Off Prime";G3<23;"Prime";TRUE;"Night Off Prime")

(may replace ; with , depending on regional setting)

alternatively:

=IFS(G3<0;"#N/A";G3<6;"Night Off Prime";G3<18;"Day Off Prime";G3<23;"Prime";G3<24;"Night Off Prime";TRUE;"#N/A")

CodePudding user response：

"The main trouble I'm finding there is how to label time between two different times such as between 11pm - 5:59am instead of just past 11pm."

Add a column H2 = G2 1/24
Then your ranges are 0 to 7/24, 7/24 to 19/24 and 19/24 to 24/24, so you don't have an interval split over a resetting of the clock to zero.