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,

May I know whether you can click on Reply>Insert image to upload some related screenshots showing the issue in your environment for our reference? If yes, some screenshots are appreciated.

Note: Remove any private information before uploading the screenshots.

I may need some time to play with some formulas to see if there are some formulas meeting your requirements. I'll update here as soon as possible.

Welcome to share any updates when you have time.

Sincerely

Cliff | Microsoft Community Moderator

Answer 2

Did this from my personal computer while working from home:

List1

List2

Answer 3

Dear Daniel.152,

Thanks for your updates and screenshots.

What you try aren't the calculated type columns. You may try the calculated columns such as Calculated1 and Calculated2 in my environment to see the result.

Calculated1: =NOW()+14

Calculated2 is to calculate the 3 workdays from Date1 and the formula should be =IF(WEEKDAY(Date1)=1,Date1+3,IF(WEEKDAY(Date1)=2,Date1+3,IF(WEEKDAY(Date1)=3,Date1+3,IF(WEEKDAY(Date1)=4,Date1+5,IF(WEEKDAY(Date1)=5,Date1+5,IF(WEEKDAY(Date1)=6,Date1+5,IF(WEEKDAY(Date1)=7,Date1+4,"")))))))

Result:

I suggest you give it a try at your side to see the result.

Welcome to share any updates when you have time.

Sincerely

Cliff | Microsoft Community Moderator

Answer 4

This won't work because I cannot take that calculated1 or calculated2 value until after the new item is saved. The whole idea behind assigning it as a default value is that it helps the user remember there is always a lead time.