SharePoint Formula: [Created]+60 days = Extension Date (but need it to be a weekday)

Question

I need help with a formula that will add [Extension], a choice field of 30, 45, or 60, to [Created], and return a date [Extension Weekday].

BUT, that future date must be a weekday.

Example: [Created] + [Extension] = [Extension Weekday], but if the result is a Saturday or Sunday, calculate it as the next Monday.

Thanks!

basic · Accepted Answer · 2020-02-20T14:24:55.363

1

You can use following formula:

=A2+B2+((WEEKDAY(A2+B2,2)=6)*2)+((WEEKDAY(A2+B2,2)=7)*1)

First weekday = 1 = Monday, last = 7 = Sunday. Change WEEKDAY function second parameter if needed.

edited Feb 20 '20 at 14:24

answered Feb 20 '20 at 14:19

basic

Thank you very much! This works perfectly. Final formula in Sharepoint: – ChasEpes Feb 20 '20 at 14:44
=[Date Created]+[Exit Extension Days]+((WEEKDAY([Date Created]+[Exit Extension Days],2)=6)*2)+((WEEKDAY([Date Created]+[Exit Extension Days],2)=7)*1) – ChasEpes Feb 20 '20 at 14:44
Hi again: So, can you tell me or write out briefly what this formula is "saying"? 6 or Saturday * 2 and 7 oe Sunday * 1...... – ChasEpes Feb 20 '20 at 15:06
Hi. This means that the day returned by the ```WEEKDAY``` function is compared with Saturday, if the comparison returns ```TRUE``` (which is 1) then the product of 2 returns 2, if ```FALSE``` (which is 0), the result is 0. Analogous to Sunday. – basic Feb 20 '20 at 15:26
Great! Thanks again for your help and talent! – ChasEpes Feb 20 '20 at 18:06

score 0 · Answer 2 · answered Feb 20 '20 at 14:10

0

You might use following functions:

TEXT() : =TEXT(20/02/2020;"dddd") shows the name of that day.
IF(condition;result)
OR(condition1;condition2)

So you'll need something like:

=IF(OR(TEXT(<date>;"dddd")="Saturday";TEXT(<date>;"dddd")="Sunday");"Monday";TEXT(<date>;"dddd"))

The formatting, day naming, ... might be language dependent, so I'll advise you to do some experimenting, but this gives you a start.

answered Feb 20 '20 at 14:10

Dominique

Thank you. Great for formatting but the calculation is not there. – ChasEpes Feb 20 '20 at 14:47
How do you mean? I understood the calculation was done, you only needed support for the weekday/weekend day distinction? – Dominique Feb 20 '20 at 15:45
Sorry, I was unclear, my formula was outlined but I needed the result to move to a weekday if it returned Saturday or Sunday. I probably misunderstood your solution. Thanks again for helping. – ChasEpes Feb 20 '20 at 18:08

2 Answers2