If time is between two times, return a value

Hi there,

I want to return a value of “Morning news” when the time from a live date falls between 5AM and 9AM.

This needs to be irrespective of date, so I made a field that parses the time from the live date field.

Currently I’ve got:

IF(AND(VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) <90100, VALUE(DATETIME_FORMAT({ADMIN - Time},‘Hmmss’)) >45900), “Morning news”, “Other”)

But that’s returning an error. I tried it without the Value parameters too.

Hi @automadien, the reason your formula doesn’t work is because you’re using wrong quotation marks. You can use this one " - Quotation Mark: U+0022 quot - Unicode Character Table or this one ' - Apostrophe: U+0027 apos - Unicode Character Table

Whereas you have used double quotation marks: “ - Left Double Quotation Mark: U+201C ldquo - Unicode Character Table

Hi Rupert!

Thanks for flagging this but unfortunately it’s still not working :slight_smile:

It worked for me after copy & pasting your formula plus changing quotation marks.

Field you’re referencing is a date & time field?

Yes, it’s a date + time field

1 Like

@automadien

Your formula can be dramatically simplified. If you only care about the hour, then just use the HOUR() function:

IF(
AND(HOUR({Time Field})>=5,HOUR({Time Field})<=9),
"Morning News",
"Other"
)

However, note that you might run into time zone issues, unless you change your original time field to always display as GMT time zone.

5 Likes

D’oh! Excellent. Thanks so much.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.