Payday can be one of eight possible days:
- the 12th (if the 13th is Good Friday,
because then the 13th, 14th and 15th form a three-day weekend)
- the 13th (if it is the last working day of the week*)
- the 14th (if it is the last working day of the week*)
- the 15th (if it is a working day*)
- the 4nd-to-last day of the month (if the 3rd-to-last day is Good Friday,
because then the last three days of the month form a three-day weekend)
- the 3rd-to-last day of the month (if it is the last working day of the week*)
- the 2nd-to-last day of the month (if it is the last working day of the week*)
- the last day of the month (if it is a working day*)
__________
* A working day is Monday through Friday
except for Good Friday.
In Excel 2013,
WEEKDAY(date, 2)
returns 1 if the
date is Monday, …, 7 if the
date is Sunday.
This is indicated as being a recent extension;
if it doesn’t work for you, say so, and we can work around it.
Using that
WEEKDAY function,
and the
EOMONTH function that tells us the last day of a month,
we can translate the above list to the following formula:
=AND(OR(AND(DAY(A1)=12,A1+1=F$1), AND(DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(DAY(A1)=15,WEEKDAY(A1,2)<=5), AND(EOMONTH(A1,0)-A1=3,A1+1=F$1), AND(EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1)), AND(A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5)), A1<>F$1)
where the date of Good Friday (April 14, 2017)
is (manually) entered into cell F1.
This formula can be crudely broken down into
=AND(
OR( AND(something1), AND(something2), AND(something3),
AND(something4), AND(something5), AND(something6)
),
something7
)
where
something1
is DAY(A1)=12,A1+1=F$1,
which tests for the first bullet
(Thursday the 12th day of the month, when the next day is Good Friday).something2
is DAY(A1)>=13,DAY(A1)<=14,OR(WEEKDAY(A1,2)=5,A1+1=F$1),
which tests for the second and third bullets
(the 13th or 14th on a Friday or the day (Thursday) before Good Friday).something3
is DAY(A1)=15,WEEKDAY(A1,2)<=5),
which tests for the fourth bullet (the 15th on a weekday).something4
is EOMONTH(A1,0)-A1=3,A1+1=F$1,
which tests for the fifth bullet
(4nd-to-last day of the month, when the next day is Good Friday).something5
is EOMONTH(A1,0)-A1<=2,EOMONTH(A1,0)-A1>=1,OR(WEEKDAY(A1,2)=5,A1+1=F$1),
which tests for the 6th and 7th bullets
(2nd- or 3rd-to-last day of the month,
on a Friday or the day before Good Friday).something6
is A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5,
which tests for the eighth bullet (last day of the month, on a weekday).something7 is A1<>F$1,
which tests for today being Good Friday and excludes it.
Without this test, we would highlight (color/shade)
Thursday, April 13 and Friday, April 14.
To test this, you might want to set F1 to each of the following dates
for 2017 and verify that the correct days are highlighted.
- This year’s actual Good Friday: April 14,
which forces payday to be on Thursday, April 13.
- January 13, which would force payday to be on Thursday, January 12.
- September 15 (or December 15),
which would force payday to be on Thursday the 14th.
- Friday, April 28, which would force payday to be on Thursday, April 27.
- September 29, which would force payday to be on Thursday, September 28.
- March 31 (or June 30),
which would force payday to be on the 2nd-to-last day of that month.
Note: If F1 is set to a date that isn’t a Friday,
incorrect results (e.g., more than two paydays in a month)
will be displayed.
