Why does comparing dates (using 'IF' function) return unexpected outcome?

Question

I have a list of dates which I want to have in 2 categories. Dates before 21-06-2024 and dates after. I have dates in a notation like this: 19-09-2024 06:45

I created an if-function but its results are not what I expected. I tried to figure out why, but I still do not understand.

This is the excel function:

=ALS(TEKST(W2;"dd-mm-jjjj")<"21-06-2024";"1";"2")

as stated, the date is in this format: 19-09-2024 06:45

I get a '1' for 19-09-2024 while this should be a 2. I get '1'-s for all dates up to 01-08-2024. 31-07-2024 returns a 2 (as expected). Then 20-07-2024 returns a 1 again. and from 30-06-2024 it returns 2. Then, at 20-06-2024 it returns 1 again.

So actually, I does only look at the day-part of the date, not the whole date.

I've been on holiday for a few weeks so my brains probably take some time to get working optimally again but I can't figure this one out.

edit: added 'datevalue' to the if-function to make the function compare the two dates.

=ALS(C2<DATUMWAARDE("21-6-2024");"1";"2")

You are comparing text not dates. What is the date format of your system? — Ike
– Ike, Commented Aug 12, 2024 at 9:16

rotabor · Accepted Answer · 2024-08-12 09:49:25Z

3

Just use

=IF(W2<DATE(2024;6;21);1;2)

Expecting W2 contains a date not a text. The display format can vary.

If W2 is text

=IF(DATEVALUE(W2)<DATE(2024;6;21);1;2)

You can compare dates as text, but you need properly express them

=ALS(TEKST(W2;"jjjj-mm-dd")<"2024-06-21";"1";"2")

Text is compared from left to right thus you need to put the most significant part of the date to left and keep the length of fields 4-2-2.

edited Aug 12, 2024 at 9:49

answered Aug 12, 2024 at 9:34

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Over a year ago

Instead of DATE you can also use a literal string and add zero to force it into a serial number: =IF(W2>"1-06-2024"+0;"1";"2")

rotabor Over a year ago

@Paul Interesting trick. It can help in some cases.

Collectives™ on Stack Overflow

Why does comparing dates (using 'IF' function) return unexpected outcome?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related