Workday Formula

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am after a bit of help with a workday formula. I have a Spreadsheet that contains the year in cell A26. In column A6 to A17 I have a list of months starting from April to March. What I am trying to do is find the first working day on or after the 8th of each month. As the year in cell A26 can change so the formula needs to apply to any year. TIA
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
In Cell B6 and copy down. Change the date forma to suit your country format.

=WORKDAY(TEXT(7&A6&$A$26,"dd/mm/yyyy"),1)
 
Upvote 0
Custom format to your preference
N.B. tested on system with International Regional Settings
Ignore the attachments; I do not not how they got there!


Date and Time.xlsm
AB
6AprilThu 9-Apr-20
7MayMon 11-May-20
8JuneTue 9-Jun-20
9JulyThu 9-Jul-20
10AugustMon 10-Aug-20
11SeptemberWed 9-Sep-20
12OctoberFri 9-Oct-20
13NovemberMon 9-Nov-20
14DecemberWed 9-Dec-20
15JanuaryMon 11-Jan-21
16FebruaryTue 9-Feb-21
17MarchTue 9-Mar-21
18
15b
Cell Formulas
RangeFormula
B6:B17B6=WORKDAY(DATE($A$26+(MONTH(1&A6)<4),MONTH(1&A6),8),1)
 
Upvote 0
In Cell B6 and copy down. Change the date forma to suit your country format.

=WORKDAY(TEXT(7&A6&$A$26,"dd/mm/yyyy"),1)
Cortexnotion, Thanks for your response. The formula did work to a degree, however, the Jan, Feb, March needs to refer to the following year as the financial year starts in April of one year to March the following year.

I would also like to know how I would modify the formula so I can use it on a column where I am trying to calculate the first working day on or after the 15th of the month.

Thanks again for your help it is much appreciated.
 
Upvote 0
Custom format to your preference
N.B. tested on system with International Regional Settings
Ignore the attachments; I do not not how they got there!


Date and Time.xlsm
AB
6AprilThu 9-Apr-20
7MayMon 11-May-20
8JuneTue 9-Jun-20
9JulyThu 9-Jul-20
10AugustMon 10-Aug-20
11SeptemberWed 9-Sep-20
12OctoberFri 9-Oct-20
13NovemberMon 9-Nov-20
14DecemberWed 9-Dec-20
15JanuaryMon 11-Jan-21
16FebruaryTue 9-Feb-21
17MarchTue 9-Mar-21
18
15b
Cell Formulas
RangeFormula
B6:B17B6=WORKDAY(DATE($A$26+(MONTH(1&A6)<4),MONTH(1&A6),8),1)
Dave,

I have looked at your formula and it doesn't appear to quite work. Perhaps I didn't explain correctly the first workday that is possible is the 8th and if the 8th fell at the weekend then it needs to be the first working day after the 8th of the month and when I copied your formula down the column, there were at least two Saturdays and one Sunday within your formula.

Thanks for your help. If you could have another look at the formula I would very much appreciate that.
 
Upvote 0
Upvote 0
Cortexnotion, Thanks for your response. The formula did work to a degree, however, the Jan, Feb, March needs to refer to the following year as the financial year starts in April of one year to March the following year.

I would also like to know how I would modify the formula so I can use it on a column where I am trying to calculate the first working day on or after the 15th of the month.

Thanks again for your help it is much appreciated.
Cortexnotion,

Thanks for your help. The formula did work perfectly, but I had to add a 1 on to the year on the last three dates to get it to work for the following year. Brilliant. Thanks Again.
 
Upvote 0
In case you actually try the formula.

Cell Formulas
RangeFormula
B6:B17B6=WORKDAY(DATE($A$26+(MONTH(1&A6)<4),MONTH(1&A6),7),1)
C6:C17C6=WORKDAY(TEXT(7&A6&$A$26+OR(A6={"January","February","March"}),"dd/mm/yyyy"),1)
D6:D17D6=B6=C6
 
Upvote 0
In Cell B6 and copy down. Change the date forma to suit your country format.

=WORKDAY(TEXT(7&A6&$A$26,"dd/mm/yyyy"),1)
Cortexnotion,

Hi again,

I have used the formula above and everything appears to be working to spec, however, I do have a little follow up question about your formula. I do have a list of bank holidays in the range A27 to A50, and I wondered how I could incorporate this into your formula? The reason that I ask is because Easter has this annoying habit of moving around and is likely to fall in the Workday range at some point.
 
Upvote 0
Excel's help for the Workday function might answer your question.
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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