# If-Then-Else for TIME range

I’m trying to build an ITE for a range of times where I get “Morning,” “Afternoon” and “Evening” returned but I can’t get it right. I was able to implement a script in my Sheet that worked great but whenever a new row was created, it didn’t implement it - it just stayed in the row above.

I’ve tried basing it off of “If NOW is on or before then (the static time)…” as well as “If morning (based off of the static time) is on or before NOW then…” but I can’t get it. Someone please help!

You can go with this.

Create a math column to derive the hour from your time. HOUR(T) with T pointing to the full timestamp.

Then from that hour number, create an If Then Else column.

If Hour is greater than 18 then Evening.
If Hour is greater than 11 then Afternoon.
If Hour is greater than 6 then Morning.
Else Evening.

Something like that.

1 Like

So, I’ve got a Math column pulling NOW for the time and I’ve got the time thresholds like this:

Should I change the times to 24 hour?

If you want a dynamic greeting, then just have one now column, you can keep everything like it is. Then make an hour column like I said, and an If Then Else to generate the greeting.

So 5am is evening?

2 Likes

Please spare me, I go to sleep very late so 6 is the start of morning for me.

4 Likes

Hey @ThinhDinh - thank you so much for your help!

I feel like I’m missing something here, though:

Current time is 0847 and it’s been converted to a whole number using a Math column. I have the ITE column setup and, to test it, I have it setup to populate “Afternoon” after 7 but it will still only show “Morning.” What am I doing wrong?

I’ve also had the time pull from NOW with the same result

Your IF statement stops after the first true statement. All of your numbers are greater than or equal to zero so it’s always going to return Morning as a result. Flip it around so it checks the largest number first.

4 Likes

haha, beat me to it… just spotted that!

3 Likes

@Jeff_Hager coming through in the clutch! THANK YOU! I was going to post a gif of Nacho Libre telling you that you’re the best but it won’t let me, so I’ll just say it -

You are the best!

1 Like

I got ya!

2 Likes

lol, that’s YOU!

1 Like

Implemented today, works a treat

2 Likes