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

2 Answers 2

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.

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
    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. 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
    Jan 28, 2021 at 13:42
  • @gd214 Yes, you are right. Added one more condition for the same week as the update. 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
    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.

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.