Using Today() function but getting unexpected results

[Shrapnel75]

Hi there, it is 8th October today, so why does the formula below return the false value? (US date format)

=if(today()>"10/01/20","Yes we are past the 1st October","No we are not past the 1st October")

I'm using a MacBook Air, but the same result happens in EXCEL or Google Sheets.

If I change the mathematic inequality to 'less than' it obviously returns the true value, but I just can't see why.

Thanks

[Pete_UK]

If your computer is set to UK regional settings, then the date will be taken as 10th January. To be on the safe side, you could use:

DATE(2020,10,1)

Hope this helps.

Pete

[alansidman]

Remove the quotation marks from the date

=IF(TODAY()>10/1/20,"Yes we are past the 1st October","No we are not past the 1st October")

[Shrapnel75]

That was it. Bugged me for ages.

I was actually combining Today() with Datedif to show the number of weeks that have progressed within a calendar month, but then fixing once the month is over. Seems that Datedif needs quotes otherwise it is treated as a number, but Today() is clearly indifferent.

=if(Today()>9/30/20,round(datedif("9/1/20","9/30/20","D")/7,1),datedif("9/1/20",today(),"D")/7)

Thank you ever so much!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Shrapnel75]

Fab - thanks again - I was just trying to see how to close this off!

[rorya]

That formula (the Today()>9/30/20 part) is not doing what you think it is. Try changing the 20 to 2050 and it will still evaluate the TRUE part. It's treating it as a division, not a date. You should use the DATE function as already suggested.

[Shrapnel75]

Thanks rorya,

you were correct - the unquoted date was being treated as an equation.

I now have the today function comparing its value to a given date using the date function and it is working as I would expect.

Thanks again.