1

I am using Microsoft Lists for our work schedule and I would like the "Due Date" column to be calculated based off the "Prepare Date" and "Number of Days" columns excluding weekends. The below formula works to calculate the due date but I'm unsure how to get it to skip weekends.

    [Number of Days]+[Prepare Date]

Thank you!

Georgia

0

If I have understood you correctly, you can loop the Number of Days every 5 days (weekends excluded) and add it to the original Due Date.

And the formula should be:

=([Prepare Date]+[Number of Days])+ROUND([Number of Days]/5,0)*2

Reference: ROUND function.


UPDATE:

Here is the updated formula:

IF(AND(WEEKDAY([Prepare Date]+[Number of Days],2)<6,[Number of Days]<5),[Prepare Date]+[Number of Days],
    IF(WEEKDAY([Prepare Date],2)=6,[Prepare Date]+[Number of Days]+ROUNDUP([Number of Days]/5,0)*2-1,
        IF(WEEKDAY([Prepare Date],2)=7,[Prepare Date]+[Number of Days]+ROUNDUP([Number of Days]/5,0)*2-2,
            [Prepare Date]+[Number of Days]+ROUNDUP([Number of Days]/5,0)*2
    )
)

References: WEEKDAY function. / ROUNDUP function.

5
  • thanks for your reply. Unfortunately that doesn't seem to work. I tested it out with our 2020 schedule and the due dates don't seem to exclude weekends. Example: Number of Days = 7, Prepare Date = 2/21/2020, Due Date = 3/1/2020. I would expect the calc'ed due date to return 3/3/2020 as 3/1/2020 is a Sunday. It seems to be skipping the 2/22-2/23 weekend correctly, however. – gd214 Jan 27 at 14:38
  • @gd214 Ok I get it now. We should be using ROUNDUP function to count one more weekend, and also rule out the situations when Prepare Date is on weekends. Please see the updated answer. – Chelsea_MSFT Jan 28 at 7:26
  • the new formula doesn't seem to work for any row where the prepare date should be in the same week as the due date. Example: Number of Days = 1, Prepare Date = 2/19/20, Due Date = 2/22/2020. Prepare date of 2/19 is on a Wednesday so Due Date should be on 2/20, a Thursday, but the result is a Saturday. – gd214 Jan 28 at 13:42
  • @gd214 Yes, you are right. Added one more condition for the same week as the update. – Chelsea_MSFT Jan 29 at 7:11
  • We're getting close but there are still some rows that are not right and I can't tell what the relationship between them are. All incorrect rows are either off +2 days or -2 days. Example 1: Number of Days = 4, Prepare Date = 5/8/20 (F), Calc Due Date = 5/12/20 (T), Actual Due Date = 5/14/20 (Th). Example2: Number of Days = 6, Prepare Date = 5/14/20 (Th), Calc'ed Due Date = 5/24/20 (Sun), Actual Due Date = 5/22/20 (F). – gd214 Jan 29 at 16:25

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.