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.

For more help with time zones, check out my sample base here: https://www.airtable.com/universe/expL1TuVyaYaInqm2/working-with-multiple-different-time-zones-in-ai...

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

[automadien]

ScottWorld:

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

D’oh! Excellent. Thanks so much.