1

I'm trying to create a payroll calendar. We get paid twice a month, on the 15th and the last day of the month, but never on a weekend or Good Friday. If the 15th or the last day falls on a weekend, payday gets shifted to the preceding Friday. If the 15th or the last day falls on Good Friday, payday gets shifted to the preceding Thursday.

How would you write a conditional formatting function to shade the payday cells red and do it for the entire year? The data set is the dates of a full year calendar on one worksheet. I'm using Excel 2016.

  • (1) You get paid twice a month, every month, right?  And never on a weekend (always shifted to the preceding Friday), right?  The initial wording of the question didn’t make that entirely clear.  (2) What version of Excel are you using?  Please do not respond in comments; edit your question to make it clearer and more complete.  Add the tag for the version of Excel you are using. – G-Man Says 'Reinstate Monica' Dec 21 '16 at 7:29
1

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.

  • I tried the formula but it did not work. I'm going to pick it apart and see if I can't figure out what is wrong. That will also help me understand it better. – Michael Harris Dec 21 '16 at 14:06
  • That worked. Thanks. It helped to format my numbers as date! – Michael Harris Dec 21 '16 at 17:13
  • I discovered a complication. We have Good Friday off which this year is April 14. Therefore, we will get paid on Thursday April 13. I suppose the same thing could happen at the end of the month as well since Good Friday (Easter) moves around. I have a formula that calculates the date for Good Friday. Is there a way to take that into account? – Michael Harris Dec 21 '16 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.