Workday Function to Stop on Last Date

scott11

New Member
Joined
Sep 13, 2016
Messages
28
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

scott11

New Member
Joined
Sep 13, 2016
Messages
28
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we could help.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top