1

I have a list in SharePoint on-premises site that has a calculated date column (named Acknowledge Due Date) with this formula:

=IF(WEEKDAY((Created))=1,(Created+3),IF(WEEKDAY((Created))=2,(Created+3),IF(WEEKDAY((Created))=3,(Created+3),IF(WEEKDAY((Created))=4,(Created+5),IF(WEEKDAY((Created))=5,(Created+5),IF(WEEKDAY((Created))=6,(Created+5),IF(WEEKDAY((Created))=7,(Created+4))))))))

I need to migrate it to a new list in SharePoint online and I need to keep the same formula in this column because users will continue to add new items to this list. But when migrating the data, how can I show the actual calculated date in this column from the old list?

For example, I have an item with Acknowledgement Due Date of 3/28/2023 (Created Date 3/23/2023 Thursday+5days). If I add this item to my new list in SharePoint online today, the acknowledgement due date will be April 5, 2023.

Is there a way to show the actual date (3/28/2023)? I'm very new to this and I'm not sure if what I'm asking is possible. Any guidance would be very much appreciated. Thanks so much!

6
  • How you are going to migrate data? are you going to use any tool like ShareGate? Commented Mar 31, 2023 at 4:21
  • Nope. I'm doing it manually. :(
    – Gen_C
    Commented Mar 31, 2023 at 4:32
  • If you are copying item manually then it won't be possible because your formula is using Created field value which will be changed if you add item manually. here possible value to migrate item using some third party tool or using Script where you can write code to set Created time Commented Mar 31, 2023 at 5:35
  • Thanks Kalpesh for taking the time looking into this and explaining it in details. Much appreciated.
    – Gen_C
    Commented Mar 31, 2023 at 6:20
  • I have added work around as answer. Please have a look and let me know whether it works for you or not Commented Mar 31, 2023 at 11:13

1 Answer 1

0

As workaround, you can create one column of type Date Time for storing Created value from old environment. e.g. CreatedOLD.

Now while migrating content you can set value to CreatedOLD value as per OLD System.

Now you can adapt your formula in such a way that if CreatedOLD does have value then it should calculate Due Date based on this field value otherwise it should use default Created column.

In that case your formula should look like as below:

=IF(ISBLANK(CreatedOLD),IF(WEEKDAY((Created))=1,(Created+3),IF(WEEKDAY((Created))=2,(Created+3),IF(WEEKDAY((Created))=3,(Created+3),IF(WEEKDAY((Created))=4,(Created+5),IF(WEEKDAY((Created))=5,(Created+5),IF(WEEKDAY((Created))=6,(Created+5),IF(WEEKDAY((Created))=7,(Created+4)))))))),IF(WEEKDAY((CreatedOLD))=1,(CreatedOLD+3),IF(WEEKDAY((CreatedOLD))=2,(CreatedOLD+3),IF(WEEKDAY((CreatedOLD))=3,(CreatedOLD+3),IF(WEEKDAY((CreatedOLD))=4,(CreatedOLD+5),IF(WEEKDAY((CreatedOLD))=5,(CreatedOLD+5),IF(WEEKDAY((CreatedOLD))=6,(CreatedOLD+5),IF(WEEKDAY((CreatedOLD))=7,(CreatedOLD+4)))))))))

See below screen shot:

enter image description here

See more details on calculated column at below link:

https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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