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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
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,910
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,910
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,283
Messages
5,635,299
Members
416,851
Latest member
zeldadav

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