I am having a situation where a date does not compare equally with the same date when constructed with date(a,b,c) formula.
- Cell D1 has a date: 26/08/2021 (August 26, 2021 - formatted as per my default locale).
- Cell E1 has the formula:
=IF(D1<DATE(YEAR(D1),MONTH(D1),DAY(D1 1)),"YES","NO").
I get NO in cell E1. I can't make out why.
There is no time included in D1's contents. In fact, =IF(D1-TRUNC(D1)=0,"YES","NO") gives me YES.
This is Microsoft Excel for Microsoft 365 MSO (Version 2204 Build 16.0.15128.20278) 64-bit.
CodePudding user response：
I have no idea what's going wrong on your computer, but I'd like to explain you how to deal with such issues in general: Excel has a formula auditing feature, which is capable of executing the formula step by step. Like this, you can have a look at what your formula should be doing and where exactly it's going wrong.
Formula auditing can be foundin the "Formulas" menu, "Formula Auditing" tab, using the "Evaluate Formula" feature. That opens a dialog box where you can evaluate every step of your formula, hereby a screenshot:
CodePudding user response：
I don't know what I was doing wrong (the original spreadsheet came from a service provider and is probably automatically generated), but simply pressing the F2 key followed by the Enter key in the date cell (D1) updated something after which the formula started working correctly.