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


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

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

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


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.