SOLVED

Excel formula help

Copper Contributor

I am trying to find the best formula for the following situation. I want to take a date, subtract a certain number of days (will vary) and have the output only display Monday - Friday. If it lands on a Saturday or Sunday I want the date to default to the prior Friday. I’m currently using the workday formula but it’s counting business days. I want to count calendar days but not display weekends in my output. Example - date 9/6 minus 10 days - output would be 8/27 which is a Sunday so I would want it to display 8/25

6 Replies

@JenAllen932 

With a date in A1:

 

=WORKDAY(A1-9, -1)

 

Format the cell with the formula as a date.

@HansVogelaar this is great. Except it’s subtracting a day on all days even if it isn’t a weekend day. Is there a way to prevent that

@JenAllen932 

the formula that I suggested returns the correct result in your example: 9/6 minus 10 days returns 8/25.

Can you provide some examples where the formula does not return the desired result, with an indication of what you expected? Thanks in advance.

Absolutely. I have a variation of dates as well as number of days. So an example of another cell would be 9/5 minus 5 days. Output should be 8/31 but that formula displays 8/30
best response confirmed by SergeiBaklan (MVP)
Solution

@JenAllen932 

Take a good look at the formula:

HansVogelaar_0-1693405458324.png

We subtract the number of days minus one from the date, then apply WORKDAY with -1 as argument.

This is perfect. I cannot thank you enough
1 best response

Accepted Solutions
best response confirmed by SergeiBaklan (MVP)
Solution

@JenAllen932 

Take a good look at the formula:

HansVogelaar_0-1693405458324.png

We subtract the number of days minus one from the date, then apply WORKDAY with -1 as argument.

View solution in original post