Workday Function to Stop on Last Date

scott11

New Member
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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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.
Excel Formula:
``=WORKDAY(A1,C1-1)``

pbornemeier

Well-known Member
=WORKDAY(A1,C1-1)

scott11

New Member
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.
Excel Formula:
``=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

New Member
=WORKDAY(A1,C1-1)
Thanks, I was putting the -1 on the outside of the parenthesis.

You are welcome.

Replies
0
Views
130
Replies
1
Views
24
Replies
0
Views
108
Replies
1
Views
30
Replies
3
Views
39

1,129,527
Messages
5,636,834
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back