Lookup with value test and conditional formatting?

[ricjen]

Windows 10, Office 2016

I’m trying to manage payments that come in from several sources. Most are monthly or weekly but I have some that are due every 28 days and come in regularly (give or take a couple of days).

So, I’ve got various columns of payment data (just a number) corresponding to various payment sources with Column 1 as consecutive dates. Monthly would work nice as I can compartmentalise each month but the 28 day ones are proving a pain to manage as I might have 2 in one month, none in another, and it’s a moving target!

I want to make sure that when I list all payments, I have an easy test that highlights (via conditional format, I'm fine with that) when a 28 day payment is made, whether it comes in within 28 +/- 3 days of the previous payment in that Column (so it's quickly obvious if I have missed any!)

Hope this makes sense…Thanks for any suggestions.

 

[braindiesel]

Can you copy and paste part of the excel sheet here to make this easier to solve?

 

[ricjen]

Thanks. This is a very simplified version. The weekly is tracked elsewhere so not a problem and I can see the monthly payments OK
I am looking for an auto flag for the column A 28 day items - 28 days is give or take a couple of days but when this gets in with other data it gets a bit messy and I end up counting using a calendar.
Thanks for your interest.

DUE	28 days	weekly	weekly	monthly	monthly	monthly
Date	A	B	C	D	E	F
01-Jan​	200
02-Jan​		130
03-Jan​					170
05-Jan​			250
09-Jan​		140
12-Jan​			290
16-Jan​		130				210
19-Jan​			260
23-Jan​		130
26-Jan​	185		250
29-Jan​				190
30-Jan​		170
02-Feb​			190
04-Feb​					180
06-Feb​		170
09-Feb​			210
13-Feb​		120
16-Feb​			220			250
20-Feb​		150
23-Feb​			110
25-Feb​	130
27-Feb​		140
28-Feb​				200
01-Mar​			170
03-Mar​					180
05-Mar​		150
08-Mar​			160
12-Mar​		160
15-Mar​			150			220
19-Mar​		160
22-Mar​			150
26-Mar​	170	150
29-Mar​			180	220
02-Apr​		180			190
03-Apr​

 

[braindiesel]

OK, so just help me understand a couple things...
1) In the second column (28 Days Date A) you have different amounts on days not 28 days apart...Are they representing different bills at a 28 day duration or a variable amount paid around the 28 day mark.
2) As the amount is variable, hard to have a formula put a number in the cell... do you just want the target cell (Day #28) highlighted and the +- 3 days highlighted a different colour to show the target window?
3)Do you need to adjust the 28 days (or the monthly for that matter) for weekends... Saturday/Sunday would target the Friday previous... that sort of thing.

Let's start with that.

 

[ricjen]

1) The payments are supposed to come in every 28 days, but its a give or take up to 3 days (due to weekends/holidays etc). Difficult to predict but +/- 3 days is fine. The amount can vary.
2) A "28 day +/- 3 counter" in another column is fine, and/or a colour highlight. I'm looking for a simple "flag" that draws my attention to make me look into an anomaly if a payment is too late (or missed).
3) I think that would be too complex as I can't get a sensible answer out of the (bureaucratic) department - they only say "payment is due every 28 days" (It isn't paid like that, but is usually close.).

I won't be looking for analysis of the numbers, just "hey, you should look at this it's late" kind of thing. My needs are simple but I'm willing to bet the solution isn't.

I hope this helps. Thank you for your interest. I really appreciate it.

 

[braindiesel]

Ok.. I am unplugged atm but will look.
Question:
First payment is on Jan 1.
Next payment is Jan 29 but it is made within the 3 plus minus on the 1st of feb...
Is the subsequent 28 days from the 29th (due date) or 1st of Feb (paid date)?