Hi Everyone,
I am not able to use logical arguments in calculated columns in my list and I don't know what I'm doing wrong.
First of all, I can't compare two dates. I have a column that is manually populated with a date (no time). I have tried to set up a calculated column to tell me if that date occurs in the past or not. IF(Column1Date<Now(),"YES","")
Sharepoint will return "YES" for every row, even when the date occurs in the past.
Example with Now() in the formula: (today is 4/4)
ColumnDate1
-date format/no time | Calculated Column:
=IF([ColumnDate1]<Now(),"YES","") |
| 4/1/2022 | YES |
| 4/15/2022 | YES |
Example where I tried comparing two columns instead of using Now() directly in the formula:
Column1Date (date format, no time) | Calculated Column
=NOW() (date format, no time) | Calculated Column:
=IF([Column1Date]<[CalculatedColumn],"YES","") |
| 4/1/2022 | 4/4/2022 | YES |
| 4/15/2022 | 4/4/2022 | YES |
I thought there must be something going on behind the scenes where the dates are being treated like numbers or converted into a strange format and it was causing the logical argument to fail, so I tried a workaround so that I could compare simple numbers rather than dates. In this case I wanted to determine if there's more than 7 days between two dates.
Example:
Column1Date (manually entered date, in date format/no time) | Column2Date
(manually entered date, date format/no time) | CalculatedColumn1 =DATEDIF([Column2Date],[Column1Date],"D") number format, 0 decimal places | CalculatedColumn2 =IF([CalculatedColumn1]>7,"YES","") |
| 4/1/2022 | 4/4/2022 | 3 | YES |
| 4/1/2022 | 4/20/2022 | 19 | YES |
I don't know why the logical arguments in the "IF" formulas don't work. They just won't calculate correctly. What am I doing wrong?
At this point I have set up my list to use the third workaround (counting number of days between dates then determining if it's more than 7 days) and that's the more pressing issue, but I would really also like to know why I can't seem to compare dates directly either.
