+1Hi 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.
Best answer by ScottWorld
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-airtable
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
+21Hi @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
+1Hi @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:
+21Hi 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?
+1It 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
+35Your 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-airtable
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
+1Your 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-airtable
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.