Can't get TODAY() to know when today is

[James_Trory1]

I have a very simple formula request that is blowing my mind right now. I don’t know why this is so difficult.

If the field {Due Date} is today, return the value “Due Today” in the formula field. First I tried:

IF({Due Date}=TODAY(),“Due Today”)

Pretty standard. But amazingly that won’t return “Due Today” if the date is today, only if the date is in the future :man_facepalming:t3:

I read here in the community forum that this would work instead:

IF(IS_SAME({Due Date},TODAY(),‘days’),“Due Today”)

Which doesn’t work either. The formula field remains blank in this case, no matter what the date is. I’ve tried ‘days’, ‘day’ and removing it completely - the formula is accepted but returns no value.

Which btw, I find really frustrating. Don’t let me save a formula that doesn’t work.

I’m so confused I don’t even know where to start trying to troubleshoot it. This is the kind of thing that puts me off using Airtable. It’s such a simple formula which in any other database software is routine. Can someone help me crack this enigma code please?

[Kamille_Parks]

For your reference, Airtable won’t let you save an improper formula. It lets you save formulas which return no value so long as they have sound syntax. I use formulas all the time which purposefully return a blank cell if conditions aren’t met.

As for why TODAY() isn’t working, I’m sure @W_Vann_Hall knows…

[IT_BeeTee]

Hi James,

Both formulas work fine for me. Have you tried just TODAY() to see what it returns? Does it really return today’s date?

Regards,

Alex

[James_Trory1]

It is 11:54PM EST, 6/4/19 and TODAY() returns 12:00AM 6/5/19 so no, it isn’t returning the correct date or time. Which might explain why =TODAY() isn’t working but the next question would be, why?

[James_Trory1]

Sorry, I should rephrase my comment. You’re correct, it does not allow improper syntax. However, there are times when there is no clear indication what values are correct within a formula.

In my original example above should I write ‘days’, ‘day’ or leave it blank? All 3 give me the same result, a blank formula field. Is that causing a problem or not? I have no idea because the formula gives me no warnings.

My issue with Airtable formulas is that I don’t know what the problem is with the formula. I get no help from the app about why a formula is failing, just that it is. My other experience with similar formula syntax in Quickbase and Tableau has been that those apps will assist me in figuring out where the probelm is.

[IT_BeeTee]

Hi James,

As I suspected, it’s due to the timezone difference between your Due Date and TODAY(). I have no idea what timezone used by TODAY() but there is a work-around to convert it to your current timezone as below

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),‘America/Chicago’),‘MM/DD/YYYY’)

A list of supported timezones can be found at https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE

After that you can use IS_SAME({Due Date}, {The new TODAY}, ‘days’) to compare as before.

Kind regards,

Alex

[James_Trory1]

Thanks for your help Alex, I do appreciate it, but I think I will give up on this for now. It’s getting to be a waste of time, I’ve been working on this for nearly 3 hours now.

The DATETIME_FORMAT formula failed until I realized that the apostrophes weren’t in the correct font after I had pasted the formula in from this forum. No indication of why it was failing, good thing I checked the formatting.

I changed the time zone to ‘America/New_York’ because I am in New York. The formula, called {Time Fix}, returns 6/4/19 as today’s date which is not correct. It’s now 12:45AM on 6/5/19.

IF(IS_SAME({Due Date},{Time Fix},‘days’,“Due Today”)) does not give me any value return, no matter what date I enter for {Due Date}.

[Justin_Barrett]

Keep in mind that formulas posted in the community might have “styled” quotes (as does the sample above) without forcing the code to use preformatted text. You’ll need to convert them to plain/non-styled quotes for the formula to operate correctly in Airtable:

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/Chicago'),'MM/DD/YYYY')

[Nicolette_Garci]

I’m having this same issue. It’s the strangest thing. When I use the following it sometimes shows yesterday’s date: DATETIME_FORMAT(SET_TIMEZONE(TODAY(), “America/Los_Angeles”), “M/DD/YYYY”) – Tried it last night and it showed me the correct date, revisited this morning and it’s showing yesterday’s date! Could this actually just be a bug? Incredibly frustrating :confused:

[Nicolette_Garci]

Hey @James_Trory1 - I know this is months later so not sure if you’re checking this thread, but I found a workaround after posting my response 4 hours ago. Wrapping your {Due Date} in the same DATETIME_FORMAT() and SET_TIMEZONE() should result in your formula working. This is what I came up with and it works:

IF(IS_SAME(DATETIME_FORMAT(SET_TIMEZONE({Due Date}, “America/Los_Angeles”), “M/DD/YYYY”),DATETIME_FORMAT(SET_TIMEZONE(TODAY(), “America/Los_Angeles”), “M/DD/YYYY”)), “1 - Due TODAY”, “”)

Its an annoying workaround, Airtable should display the correct date/time, but it works until that’s solved for.