Comparing Dates In a Logical Formula (Workaround)

Just wanted to post this in case any poor soul runs into the same issue as I did and can't find a solution.

For some reason Excel doesn't like it when you type dates into a formula. I have a table where I'd like to return a few values based on the dates in an adjacent column. To do this, I used an IFS formula,

Formula:

=IFS(C14>7/1/2019,"Summer 2019",C14<3/1/2019,"Winter 2019")

. For some reason, this will always evaluate the first logical test:

Formula:

C14<3/1/2019

as true, no matter what date is in C14.

I tried putting the dates in quotes so the equation read

Formula:

IFS(C14>"7/1/2019","Summer 2019",C14<"3/1/2019","Winter 2019")

. But then it would just evaluate everything as if the second logical was true. (Ie. It put "Winter 2019" for everything.

I found a workaround was to put the dates in their own cells, and reference the cell, as opposed to typing the date in manually. So the formula now read

Formula:

=IFS(ISBLANK(C12),"",C12>$F$5,"Summer 2019",C12<$E$5,"Winter 2019")

where the value in E5 was 3/1/2019 and F5 was 7/1/2019.

Similarly, if the dates are written inside the formula with the DATE function, it works as well.

Formula:

DATE(year,month,day)

That is not a date, it is a numeric expression. You are comparing C14 to 7 divided by 1 divided by 2019, which is some small number less than 1. C14 as date will always be greater than 1, hence False.

DATE is the best way to go IMO< but you can hardcode the date string and compare, like so

C14<--"2019-07-01"

Originally Posted by TMS

Using DATE is the way to go as it will avoid any issues/confusion with Regional Settings.

so does --"2019-07-01"

Originally Posted by TMS

Fair enough, but maybe less intuitive?

Far less intuitive . I agree that DATE is the best way, I said so in my reply, but as always with Excel, that poor old cat can be skinned many ways.

I'm with Trevor on this one, Bob!

Less experience users might not know if --"2019-07-01" was July 1st or January 7th so might it has potential to cause confusion, at least the DATE function promts you to enter it in the correct order.

Most people will enter a date in their preferred format, dd/mm/yy in the UK, mm/dd/yy in the US, --"01-07-2019" will return the correct date in the UK, but what if the file is sent to a colleague in the US? As the date is a text string, it will not be adapted to regional settings, so now the formula will give incorect results.

@Cryptikfox, your formula has a hole in the results, dates from 3/1/19 and 7/1/19 will return #N/A

For what it appears that you are tryng to do, I would consider a formula on the basis of

Formula:

=LOOKUP(MONTH(EOMONTH(C14,1)),{1,4,7,10},{"Winter ","Spring ","Summer ","Fall "})&YEAR(EOMONTH(C14,-2))

but with the hole in the results it is not clear exactly where each season should start and end.

Originally Posted by jason.b75

Less experience users might not know if --"2019-07-01" was July 1st or January 7th ...

Not disagreeing that DATE is better, but totally disagree with you on this point. yyyy-mm-dd is an ISO standard, recognised across the world. I understand that some parts of the world confusingly see a date of 07/01/2019 as 1st July, but nobody sees 2019-07-01 as 7th Jan.

Originally Posted by Bob Phillips

but nobody sees 2019-07-01 as 7th Jan.

If nobody sees it, then how did I see it to comment on it? Someone working for ms must have seen it as well, otherwise YDM wouldn't be allowed for in the text to columns date format options.

Even if it is an ISO standard, you shouldn't assume that everybody knows it. Outside of the forum, it's a standard that I've never encountered or had cause to use.

Given how many threads we see that are far from standard...

Apologies if this sounds like I'm trying to prove you wrong, Bob. Curiosity was getting the better of me, so I looked into it a bit more.

Following up on the last couple of posts, found this on wikipedia

Gregorian, year-day-month (YDM)
This date format is used in Kazakhstan, Latvia, Nepal, and Turkmenistan. According to the official rules of documenting dates by governmental authorities,[3] the date format in Kazakh is written as YYYY.DD.MM, e.g. 2006.05.04 or 2006 5 April (Kazakh: 2006 жылғы 05 сәуір)