 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]

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.

0 Replies