Date function workdays only

Benjaminmin

Board Regular
Joined
Nov 20, 2009
Messages
116
Hi.

I would like an excel formula in say B1 to minus a month from the date A1, but only return a workday. If the date returned is not a workday, I would like it to return the next working day (positive only).

Thank you for your help.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this:
=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))=7,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))+2,IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))=1,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))+1,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))))

It's not the neatest of formulas but it seems to work.
 
Upvote 0
A shorter formula using EDATE (and there may still be a better way to do this) is:
=IF(WEEKDAY(EDATE(A1,-1),2)=6,EDATE(A1,-1)+2,IF(WEEKDAY(EDATE(A1,-1),2)=7,EDATE(A1,-1)+1,EDATE(A1,-1)))

Basically it's saying that if the weekday of the data in A1 minus 1 month is 6 (Saturday) then subtract 1 month and add two days, if it's 7 (Sunday), then subtract 1 month and add one day, otherwise (Monday to Friday), just deduct the single month.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top