Help

If time is between two times, return a value

Topic Labels: Formulas
Solved
Jump to Solution
1848 6
cancel
Showing results for 
Search instead for 
Did you mean: 
automadien
6 - Interface Innovator
6 - Interface Innovator

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
ScottWorld
18 - Pluto
18 - Pluto

@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 

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

ScottWorld
18 - Pluto
18 - Pluto

@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 

D’oh! Excellent. Thanks so much.