Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

If time is between two times, return a value

Topic Labels: Formulas
Solved
Jump to Solution
322 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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 :slightly_smiling_face:

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

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

D’oh! Excellent. Thanks so much.