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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
=WORKDAY((7&A4&$A$23+OR(A4={"January","February","March"})),1,A27:A50)

Excel Help
Syntax
WORKDAY(start_date, days, [holidays])
Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
 
Upvote 0
part of your Holiday List

Date and Time.xlsm
AB
27Thu Nov 26, 2020Thanksgiving
28Fri Dec 25, 2020Christmas
29Mon Dec 28, 2020Boxing day
30Fri Jan 1, 2021Jan-01
15b
 
Upvote 0
=WORKDAY((7&A4&$A$23+OR(A4={"January","February","March"})),1,A27:A50)

Excel Help
Syntax
WORKDAY(start_date, days, [holidays])
Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
Dave, I have just used your formula above and currently it appears to be working fine. I have just got to work through a few years to check that it is omitting the correct days over Easter. Thanks again and I will let you know how I get on. Kudos to you.
 
Upvote 0
Dave, I have just used your formula above and currently it appears to be working fine. I have just got to work through a few years to check that it is omitting the correct days over Easter.
I have just checked some of the upcoming years and for 2022 Good Friday is on 15th April and the date displaying according to your formula is the 19th. As this refers to something financial it would be better if this happened on the day before (14th). I know I initially said that this column needs to be between the 15th and 17th in a normal year, however, in the years when Good Friday falls on the 15th of April then ideally the date that needs to display in the previous day i.e. 14th.
 
Upvote 0
What formula are you using?
The formulas suggested are for the Workday after the 7th.
Your post does not mention " I know I initially said that this column needs to be between the 15th and 17th in a normal year, however, in the years when Good Friday falls on the 15th of April then ideally the date that needs to display in the previous day i.e. 14th

You can consider either of the following. These consider that you want the working day after the 14th unless the 15th is a holiday.
These formula can be more concise if you have the NEW Let function or if you want to use a UDF.

=IF(DAY(WORKDAY((14&A3&$A$23+OR(A3={"January","February","March"})),1,A27:A50))=18,WORKDAY((14&A3&$A$23+OR(A3={"January","February","March"})),1,A27:A50)-4,WORKDAY((14&A3&$A$23+OR(A3={"January","February","March"})),1,A27:A50))

=IF(DAY(WORKDAY((14&A3&$A$23),1,A27:A50))=18,WORKDAY((14&A3&$A$23),1,A27:A50)-4,WORKDAY((14&A3&$A$23),1,A27:A50))
 
Upvote 0
Date and Time.xlsm
AB
21Thu 14-Apr-22Thu 14-Apr-22
22
232022
24April
2514Thu 14-Apr-22
26
15b
Cell Formulas
RangeFormula
B21B21=IF(DAY(WORKDAY(A21,1,A27:A45))=18,WORKDAY(A21,1,A27:A45)-4,WORKDAY(A21,1,A27:A45))
B25B25=IF(DAY(WORKDAY(A25&A24&A23,1,A27:A50))=18,WORKDAY(A25&A24&A23,1,A27:A50)-4,WORKDAY(A25&A24&A231,A27:A50))
 
Upvote 0
Are you only concerned if Good Friday is on the 15th?
What range of dates are relevant?

Date and Time.xlsm
ABCD
172022
18April
1914Thu 14-Apr-22
20
21Thu 14-Apr-22Thu 14-Apr-22Thu 14-Apr-22Thu 14-Apr-22
22
15b
Cell Formulas
RangeFormula
B19B19=IF(DAY(WORKDAY(A19&A18&A17,1,A27:A50))=18,WORKDAY(A19&A18&A17,1,A27:A50)-4,WORKDAY(A19&A18&A231,A27:A50))
B21B21=IF(DAY(WORKDAY(A21,1,A27:A45))=18,WORKDAY(A21,1,A27:A45)-4,WORKDAY(A21,1,A27:A45))
C21C21=WORKDAY(A21,1,A27:A45)-OR(A17={2022,2033,2044})*4
D21D21=WORKDAY(A21,1,A27:A45)-OR(YEAR(A21)={2022,2033,2044})*4
 
Upvote 0
Dave,
Are you only concerned if Good Friday is on the 15th?
What range of dates are relevant?

Date and Time.xlsm
ABCD
172022
18April
1914Thu 14-Apr-22
20
21Thu 14-Apr-22Thu 14-Apr-22Thu 14-Apr-22Thu 14-Apr-22
22
15b
Cell Formulas
RangeFormula
B19B19=IF(DAY(WORKDAY(A19&A18&A17,1,A27:A50))=18,WORKDAY(A19&A18&A17,1,A27:A50)-4,WORKDAY(A19&A18&A231,A27:A50))
B21B21=IF(DAY(WORKDAY(A21,1,A27:A45))=18,WORKDAY(A21,1,A27:A45)-4,WORKDAY(A21,1,A27:A45))
C21C21=WORKDAY(A21,1,A27:A45)-OR(A17={2022,2033,2044})*4
D21D21=WORKDAY(A21,1,A27:A45)-OR(YEAR(A21)={2022,2033,2044})*4
It is only if Good Friday falls on the 15th. The cell that contains the formula that refers to the 15th, 16th or 17th is in cell F5. The range that contains the bank holidays is in A27 to A36. and in column C27 to c37 contains the names of the bank holiday. Cell A27 contains the date of Good Friday, so if A27 = 15/04/yyyy then cell F5 needs to be A27-1. Otherwise the formula works to spec.

Cell A27 contains the following formula to calculate Good Friday A27 = =DOLLAR(("4/"&J)/7+MOD(19*MOD(J,19)-7,30)*14%,)*7-8. In the formula J is the range that contains the year in question.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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