How can I use TODAY() as part of a formula checking against a list of dates in Google Sheets?

Posted on

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common formula parse errors in Google Sheets, like How can I use TODAY() as part of a formula checking against a list of dates in Google Sheets?, and how to fix it.

I’m trying to use the TODAY() formula to check against a long column of date cells, and mark them as ‘LIVE’ if the given date is less than (or equal to) today. Crucially though, if the date cell is greater than today, or left blank, it should be marked as ‘NO’ (or just left blank).

So I guess I’m trying to say:

If A1 = less than or equal to today then ‘LIVE’, if not or blank then ‘NO’.

I’ve tried using =A1<TODAY() and this works, except if A1 is blank in which case it still marks it as TRUE.

Can anyone help? I’m very much a beginner with formula and I can’t seem to find the right way to do it.

Answer :

Dates are basically numbers and Google identifies blanks as 0 when comparing to numbers, thus you have to add check for blanks.

=ARRAYFORMULA(IF(A:A="","NO",IF(A:A<=TODAY(),"LIVE","NO"))) 

Leave a Reply

Your email address will not be published.