2

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

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
6
  • 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
    Commented Jan 27, 2021 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
    Commented Jan 28, 2021 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
    Commented Jan 28, 2021 at 13:42
  • @gd214 Yes, you are right. Added one more condition for the same week as the update.
    – Chelsea_MSFT
    Commented Jan 29, 2021 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
    Commented Jan 29, 2021 at 16:25
0
  • If the "prepare date" plus "number of days" is a weekday, output the sum.
  • Otherwise, if "prepare date" plus "number of days" is a Saturday, add an extra two days to the sum & output.
  • Otherwise, "prepare date" plus "number of days" is Sunday, so add an extra day to the sum & output.
IF(WEEKDAY([Prepare Date]+[Number of Days], 2) < 6, 
  [Prepare Date]+[Number of Days], 
  IF(WEEKDAY([Prepare Date]+[Number of Days], 2) = 6, 
    [Created]+[Number of Days]+2, 
    [Prepare Date]+[Number of Days]+1
  )
)

Note: Don't be misled by the WEEKDAY function. It provides the day of the week including Saturday & Sunday, not whether the date you have provided is a weekday (as opposed to a weekend day). The second parameter to the WEEKDAY function tells the function when your week starts and whether the number returned is zero or 1 based. In this case I am telling WEEKDAY to start on a Monday and for Mondays to return 1.

Improve this answer

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.