Conditional formatting based on bi-monthly payday criteria

This will give pay days allowing for weekends

in B1

=IF(WEEKDAY(A1,2)>5,A1-WEEKDAY(A1,2)+5,A1)

where Column A contains list of 15/30 dates and B contains adjusted dates i.e. allowing for W/E dates in A

You could then compare your dates vs the table (column B) above.

Sorry, I wasn't able to add it with your instructions. Instead of "Manage file", I have "Manage Attachments", I'm able to upload it, but there is no "Done" button, just "Close this windows", then I'm not able to see it anywhere to add it to this post.

Thanks for your fast reply, but I don't want to have to manage another table with listing of dates ... I can be wrong 1 time and will affect the display. I really want it to be done based on my job payroll criteria.

I've done this to find the payday based on the date :
=OR(DAY($H3)=15,DAY($H3)=30,DAY($H3)=IF(DAY(EOMONTH($H3,0))<30,IF(DAY($H3)=DAY(EOMONTH($H3,0)),DAY($H3),""),""))
But this show me the date evan if it's the weekend and I cannot figured where I can add criteria to move the weekend payday to the last friday as the last payday criteria. It's mostly done ... just need other eyes.

You cannot change data with CF: it only returns a TRUE or FALSE value.

Hopefully file now attached

Thank you very much for your help!

I know I am a little late to the party, but for anyone else looking for an alternate solution I have a slightly more complex solution that accounts for paying on 15th & EOM or paying ahead of that when the 15th/EOM falls on either a weekend or a company holiday.

My company, for instance, gives us both Thanksgiving and Black Friday off, and EOM falls on the weekend this year; therefore, my formula had to account for being paid up to (4) days prior to EOM. I don't believe there is any instance where it would be 5 days prior, but that is why I added "HELP" as the "if all else fails" return value. I'm sure there is a more succinct way to accomplish this, but nested IF-statements work so I'm happy with it.

STEPS:

Set up a named range (highlight range, "formulas" tab, "Define Name") that contains all company-observed holidays; I names mine "HOLIDAYS2019" in my formula example below...it lives on another worksheet, but that isn't crucial.

**All Headers are in row 1**
My "Month" Column (A) has the following...

A2: 1/15/2019
A3: =DATE(2019,MONTH(A2),DAY(EOMONTH(A2,0)))
A4: =DATE(2019,MONTH(A3)+1,15)
A5: =DATE(2019,MONTH(A4),DAY(EOMONTH(A4,0)))

Select A4 & A5 and then copy/paste down until you have DEC 15 & 31. I then changed the formatting for that column was only "MMM" while the actual cell values were still the 15th & EOM.

I am exporting this to my google calendar, so I made a dynamic title column that reflected how I wanted to call out the payroll ("1st {MONTH} PayDay" or "2nd {MONTH} PayDay")
; feel free to exclude this, but that is why I am skipping column B here.

My "PayDays" Column (C)has the following...
C2: =IF(WORKDAY(A2-1,1,HOLIDAYS2019)=A2,A2,IF(WORKDAY(A2-2,1,HOLIDAYS2019)=A2-1,A2-1,IF(WORKDAY(A2-3,1,HOLIDAYS2019)=A2-2,A2-2,IF(WORKDAY(A2-4,1,HOLIDAYS2019)=A2-3,A2-3,"HELP"))))
Copy/Paste the formula in C2 all the way down to the end of you date column range.

*the explanation for using "workday() to validate a ref date comes from here: exceljet {dot} net/formula/date-is-workday
the closest solution I saw to what I did here had the user creating a column of EVERY DAY of the year and then validating if it was a payday...oy!

Hope this helps someone else!

-Borgem