Workday Function to Stop on Last Date

[scott11]

Hello,

I have the following formula in cell B1: =WORKDAY(A1,C1), whereas A1 is a date and C1 is a whole number. However, when I have the date in A1 start out on a Monday, the formula returns a date of the following Monday if I place a 5 in C1 and not on that Friday. Basically, I'm want the date to stop on the last day of work and not the following business day. The new formula would also have to account for dates that start and stop in the middle of the week as well.

Example:
Correct - 2/1/2021 5.00 2/5/2021
Incorrect - 2/1/2021 5.00 2/8/2021

 

[Joe4]

I have the following formula in cell B1: =WORKDAY(A1,C1), whereas A1 is a date and C1 is a whole number. However, when I have the date in A1 start out on a Monday, the formula returns a date of the following Monday if I place a 5 in C1 and not on that Friday. Basically, I'm want the date to stop on the last day of work and not the following business day. The new formula would also have to account for dates that start and stop in the middle of the week as well.

Since you are counting the first day, you just need to subtract one day, so you are really going ahead 4 Workdays, not 5, i.e.

=WORKDAY(A1,C1-1)

 

[pbornemeier]

=WORKDAY(A1,C1-1)

 

[scott11]

Since you are counting the first day, you just need to subtract one day, so you are really going ahead 4 Workdays, not 5, i.e.

=WORKDAY(A1,C1-1)

I need to show the 5 in my sheet though to indicate how many days are being worked, otherwise other employees will think it will only be 4 days.

 

[Joe4]

I need to show the 5 in my sheet though to indicate how many days are being worked, otherwise other employees will think it will only be 4 days.

Right. You type 5 in cell C1.
The subtraction of 1 is happening in the formula, like we showed. So the user will never see the 4 (they will still see 5).
So you are just changing the formula, not the data.

Did you try it?

 

[scott11]

=WORKDAY(A1,C1-1)

Thanks, I was putting the -1 on the outside of the parenthesis.

 

[Joe4]

You are welcome.
Glad we could help.