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(
```*something*_{1}), AND(*something*_{2}), AND(*something*_{3}),
AND(*something*_{4}), AND(*something*_{5}), AND(*something*_{6})
),
*something*_{7}
)

where

*something*_{1}

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).
*something*_{2}

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).
*something*_{3}

is `DAY(A1)=15,WEEKDAY(A1,2)<=5)`

,
which tests for the fourth bullet (the 15th on a weekday).
*something*_{4}

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).
*something*_{5}

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).
*something*_{6}

is `A1=EOMONTH(A1,0),WEEKDAY(A1,2)<=5`

,
which tests for the eighth bullet (last day of the month, on a weekday).
*something*_{7}

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.