0

I am trying to compare two date columns in SharePoint. I need to update the created date field to a different date, but only on documents that have a scan date less than the created date. So I have a formula in a date update column thatstates =IF([Scan Date]<[Created],"Yes"), but when I apply it, it says that all of the documents have a scan date less than the created date. So I thought ok, that is not working, I will change it to say if the dates are equal to say “No” and the formula I have is =IF([Scan Date]=Created,"NO","YES") and the results are all “No”. So the formula only half works. What am I doing wrong? Any help would be greatly appreciated!

9
  • try =IF([Scan Date]<[Created],"Yes", "No"). When you compare [Date1]=[Date2], most likely the time part of the dates are not equal and giving you all "No"s. Do you have empty [Scan Date] values? Feb 18 at 20:39
  • @MatiurRahman I tried that before and its the same results. It changes all to "Yes" and no, I do not have an empty scan date value. All the fields have a date.
    – Louis Do
    Feb 18 at 21:07
  • What's the type of your calculated field? So, if [Scan Date] is less than the [Created] then the formula should return "Yes", otherwise "No", correct? [Created] date is the system date here, right? Feb 18 at 21:13
  • @MatiurRahman- I just created a new calculated column with that formula to return a singe line of text (not sur if that is what you were asking about type of calculated field). And yes that is what I want the formula to do.
    – Louis Do
    Feb 18 at 21:18
  • Not sure what is the difference in your environment. It works for me. Let me know if your conditions/column types are different from mine. Feb 18 at 21:31
0

The following worked for me, where [Scan Date] is Date and Time Column, [Created] is a System column shows when items are created and [ScanStatus] Calculated column outputs Single line of text

Does it match your conditions?

=IF([Scan Date]<Created,"Yes","No")

enter image description here


Updated the formula

to show date comparisons: If column Date and Time format is Date Only then [Date1]=[Date2] If the format is Date and Time use as shown below:

=IF(DATE(YEAR([Scan Date]),MONTH([Scan Date]),DAY([Scan Date]))=DATE(YEAR(Created),MONTH(Created),DAY(Created)),"Yes","No")

enter image description here

2
  • BINGO- THIS WORKED!! THANK YOU SO MUCH MATIUR! Now I know that the column format was causing the issue. You just saved me hours of work! I'm new to this SharePoint Admin stuff and all this forum, so if you have a moment I would appreciate it if you could explain to me how you came up with that formula. AGAIN THANK YOU!
    – Louis Do
    Feb 19 at 14:07
  • Please mark the questions as answered Feb 19 at 14:09
0

I tested with similar setup with Matiur and it also works in my end. Try with the formula below.

=IF(([Scan Date]<[Created Date]),"Yes","No")

enter image description here

1
  • Thanks for you help Jerry- It was a date and time format that's why it wasn't working. Matuir gave me the updated formula and it worked! Thank you for your time!
    – Louis Do
    Feb 19 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.