Highlighting dates in column that land within the next pay period

[tsadams23]

I have a spreadsheet where i keep track of all my upcoming bills and it would be handy to have some sort of conditional formatting so i know if the bill lands on the current pay period or the following one. It's a pretty basic setup on one sheet I have the bill information

Bill Due Date Amt Due Amt Paid Date Paid
Water 10/15/2015 50.00
Electric 10/23/2015 150.00
etc
etc

Then in another sheet i have a formula that calculates the pay days for the current year. We're paid Biweekly and i have a simple table setup for that as well

Pay Period Pay Date
1 1/8/2015
2 1/22/2015
etc
etc

I'd like to conditionally format the Bill Name or the Due date, doesnt really matter to me, but I'd like it to highlight one color if the due date lands on the upcoming pay date, and another color if it lands on the pay date after that, just so I know if i can pay some bills ahead of time or not depending on the amount due and which pay period it lands in.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this. First, here is my Pay Days sheet

Excel Workbook
	A	B
1	Pay Period	Pay Date
2	1	08-Jan-2015
3	2	22-Jan-2015
4	3	05-Feb-2015
5	4	19-Feb-2015
6	5	05-Mar-2015
7	6	19-Mar-2015
8	7	02-Apr-2015
9	8	16-Apr-2015
10	9	30-Apr-2015
11	10	14-May-2015
12	11	28-May-2015
13	12	11-Jun-2015
14	13	25-Jun-2015
15	14	09-Jul-2015
16	15	23-Jul-2015
17	16	06-Aug-2015
18	17	20-Aug-2015
19	18	03-Sep-2015
20	19	17-Sep-2015
21	20	01-Oct-2015
22	21	15-Oct-2015
23	22	29-Oct-2015
24	23	12-Nov-2015
25	24	26-Nov-2015
26	25	10-Dec-2015
27	26	24-Dec-2015
28
Pay Days

Now, in the Bills sheet, select A2:B?? and apply the two conditional formatting formula rules as shown.
Green means the bill is due on the next pay day or up until the day before the following pay day.
Blue means the bill is due in the next pay period after that.

Excel Workbook
	A	B
1	Bill	Due Date
2	Water	20-Nov-2015
3	Electric	28-Dec-2015
4	Phone	31-Oct-2015
5
Bills

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =MATCH($B2,'Pay Days'!$B$2:$B$27)-MATCH(TODAY(),'Pay Days'!$B$2:$B$27)=2	Abc
A2	2. / Formula is =MATCH($B2,'Pay Days'!$B$2:$B$27)-MATCH(TODAY(),'Pay Days'!$B$2:$B$27)=1	Abc

 

[Mott31]

I am looking to do this exact same thing, but using your example i cannot get it to work. could you post a example worksheet that i could reverse engineer?

 

[Peter_SSs]

I am looking to do this exact same thing, but using your example i cannot get it to work. could you post a example worksheet that i could reverse engineer?

Welcome to the MrExcel board!

We cannot attach actual workbooks in this site. In any case I have given all the data, layout and Conditional Formatting formulas for you to copy/paste (with a little bit of cleaning up) to a fresh workbook to test. Of course, you would have to use current year dates instead of dates from 2015 because the Conditional Formatting formulas are calculating from the current date TODAY()

If you still can't get it to work you will have to provide more details about in what way is it not working (eg not highlighting anything, highlighting everything, highlighting some things but they are the wrong things).

Ideally post the sample data you are using as I did above - help for that is available in my signature block below.

 

[Mott31]

Welcome to the MrExcel board!

We cannot attach actual workbooks in this site. In any case I have given all the data, layout and Conditional Formatting formulas for you to copy/paste (with a little bit of cleaning up) to a fresh workbook to test. Of course, you would have to use current year dates instead of dates from 2015 because the Conditional Formatting formulas are calculating from the current date TODAY()

If you still can't get it to work you will have to provide more details about in what way is it not working (eg not highlighting anything, highlighting everything, highlighting some things but they are the wrong things).

Ideally post the sample data you are using as I did above - help for that is available in my signature block below.

OK i got it. First i though the formula would highlight current pay period bills due, i doesn't it highlights next period and the period after that.

Second.....well i have no idea. I couldn't get it to highlight anything but after starting from scratch the 5th time now it seems to work. Thank You very much for your help. I have managed to automate the checkbook so much now even the wife understands where all the money goes.

 

[Peter_SSs]

OK i got it. Thank You very much for your help.

Good news. You're welcome.

 

[Mott31]

Peter, so here i am again,,,hard drive crashed and now im trying to rebuild my work again. You kindly posted the formulas and examples above for me to workout but i cannot figure out how to paste them into a worksheet to edit and look at. I copy and when i go to paste it only gives me the option to paste text only which of course is no help. What am i missing here?

 

[Peter_SSs]

What am i missing here?

You are missing two things:

1. A backup of your work!!!
   
   
2. The forum has had a software upgrade and some information, like this conditional formatting, has been lost in the transition.

I don't have the original workbook either so I don't know exactly what I posted. Does this do what you want?

Mott31 2020-02-16 1.xlsm
	A	B
1	Pay Period	Pay Date
2	1	8-Jan-20
3	2	22-Jan-20
4	3	5-Feb-20
5	4	19-Feb-20
6	5	4-Mar-20
7	6	18-Mar-20
8	7	1-Apr-20
9	8	15-Apr-20
10	9	29-Apr-20
11	10	13-May-20
12	11	27-May-20
13	12	10-Jun-20
14	13	24-Jun-20
15	14	8-Jul-20
16	15	22-Jul-20
17	16	5-Aug-20
18	17	19-Aug-20
19	18	2-Sep-20
20	19	16-Sep-20
21	20	30-Sep-20
22	21	14-Oct-20
23	22	28-Oct-20
24	23	11-Nov-20
25	24	25-Nov-20
26	25	9-Dec-20
27	26	23-Dec-20
Sheet1

Mott31 2020-02-16 1.xlsm
	A	B
1	Bill	Due Date
2	Water	6-Mar-20
3	Electric	28-Jun-20
4	Phone	24-Feb-20
Bills

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B4	Expression	=AGGREGATE(14,6,Sheet1!$B$2:$B$27/(Sheet1!$B$2:$B$27<=$B2),1)=AGGREGATE(15,6,Sheet1!$B$2:$B$27/(Sheet1!$B$2:$B$27>=TODAY()),2)	text	NO
A2:B4	Expression	=AGGREGATE(14,6,Sheet1!$B$2:$B$27/(Sheet1!$B$2:$B$27<=$B2),1)=AGGREGATE(15,6,Sheet1!$B$2:$B$27/(Sheet1!$B$2:$B$27>=TODAY()),1)	text	NO