SharePoint List date column default value problems

Question

Hello, I've been experiencing some issues assigning a default value to my date-type column using basic and complex calculations. I currently cannot post this question with screenshots, there is some issue with uploading pictures (I suspect it's my company's security environment interfering).

My scenario is that List 1 is being used as an internal work order ticketing system. We have a required lead time due to constraints with material procurement so we need the date-type column named Onsite Date to always default to 2 weeks from the current date. Similarly, I have a List 2 which needs a minimum of 3 business days lead time for its Start Date.

List 1 should be very simple but it has frequently failed to do proper calculations after the first few days or week after editing the column and saving. I've been using Default Value >> Calculated value: =TODAY()+14; =NOW()+14; =VALUE(TODAY())+14; =VALUE(TODAY()+14); =VALUE(NOW())+14; =VALUE(NOW()+14)

List 2 was obviously going to be a bit complex since the stipulation for lead time is business days. I came up with the following formula since there are times when that type of work order would be created during a weekend: =VALUE(NOW())+IF(WEEKDAY(NOW(),2)>5,8-WEEKDAY(NOW(),2),0)+3. I also tried variations of that formula, changing out NOW() for TODAY(), the placement of the closing parenthesis on the VALUE function, and entirely removing the VALUE function, with the same problem returning after a few days or week.

The problem is common with both lists, they inevitably end up calculating less days than expected: today List 1 displayed August 30, should've been September 4 and List 2 should've been August 24 but returned last Friday August 18. The most perplexing thing about this problem is that it occurs inconsistently across our user base of about 100 people, with most users seeing the problem being on PC. So far I have not received any complaints about mobile users, they're all on iPads using the built-in mobile browser for Procore as their starting point.

My question is what can I do to resolve this? Am I missing something or misreading documentation? Do I really need to use PowerApps? Trying to avoid PowerApps due to lack of skill for developing a PowerApp in an efficient time frame, along with potentially increasing cost to our company.

Answer 1

Dear Daniel.152,

Based on my test, the formula above should work in the default value of the Date and Time columns.

DefaultDateA: =NOW()+14

DefaultDateB: =IF(WEEKDAY(NOW())=1,NOW()+3,IF(WEEKDAY(NOW())=2,NOW()+3,IF(WEEKDAY(NOW())=3,NOW()+3,IF(WEEKDAY(NOW())=4,NOW()+5,IF(WEEKDAY(NOW())=5,NOW()+5,IF(WEEKDAY(NOW())=6,NOW()+5,IF(WEEKDAY(NOW())=7,NOW()+4,"")))))))

Result:

Please create a new list and see if they work in your environment.

Welcome to share any updates when you have time.

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Thank you for your suggestion, I couldn't create a new list other than for testing as I was forced to deploy my solution before I was done testing some features our userbase wanted.

With the test list I created for my requirement of 3 business days (List2), I noticed that your formula did work but it was very hard to modify (sadly a likely need in my organization) since the input box is so small. Your formula did give me some ideas and I decided to eliminate the use of the optional argument on WEEKDAY the same way you did. Also, instead of the nested IF statements, I went with CHOOSE and I'm getting the same results! New formula: =NOW()+CHOOSE(WEEKDAY(NOW()),3,3,3,5,5,5,4)

As for the other list with requirement of two weeks (List1), I am going to revert to =NOW()+14 and monitor its behavior. However, I think I'll end up using CHOOSE again because there are times when some managers create an item on the weekend, but we cannot start during a weekend.

Answer 3

Dear Daniel.152,

Thanks for your updates and I'm glad to hear that the issue has been resolved in your environment.

Welcome to post a new thread on our forum at your convenience if you encounter any issues on your environment.

Have a nice day!

Sincerely

Cliff | Microsoft Community Moderator