If time is between two times, return a value

[automadien]

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.

[Rupert_Hoffsch1]

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

[automadien]

Hi Rupert!

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

[Rupert_Hoffsch1]

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

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

[automadien]

Yes, it’s a date + time field

[ScottWorld · Accepted solution]

@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.

[automadien]

ScottWorld:

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

D’oh! Excellent. Thanks so much.