date Macro

[tonyk1051]

Hi,

I havent the slightest clue as to how this macro would look like...but below is what is needed
there are 5 date range catergories (numbers are the days that passed)
>365
90-365
60-90
30-60
0-30

In column H are dates a request is sent
I need in column I, how old that request using column H as a refernce


for example

column in H column I
3-10-21 0-30
2-15-21 30-60
1-15-21 60-90
12-17-20 90-365
12-19-19 >365

also theres two tricky parts, for vendors that appear multiple times, I would like for column I to keep the oldest date for all of them so for example

column B column H Column I
best buy 3-10-21 0-30
best buy 2-15-21 30-60
best buy 12-19-19 >365

should be like this below

column B column H Column I
best buy 3-10-21 >365
best buy 2-15-21 >365
best buy 12-19-19 >365

also not every vendor is listed at they’re a lot more, im not sure if you need me to give you every single vendor though, please let me know

[p45cal]

You don't need a macro for this. In cell I2:

=VLOOKUP(TODAY()-MAXIFS($H$2:$H$1935,$B$2:$B$1935,B2),{-1,"0-30";30,"30-60";60,"60-90";90,"90-365";365,">365"},2,1)

copied down.

I've put this formula in column P of the attached.

You might want to tweak it a bit to get the thresholds exactly as you want them, so there's a test-bed at cell T2, referring to a lookup table at cell W2 which also serves to show how the formula was derived. Adjust the thresholds in column W to get the results you want to see in column U. When satisfied, you can do away with the lookup table by using this formula in P2/I2:

=VLOOKUP(TODAY()-MAXIFS($H$2:$H$1935,$B$2:$B$1935,B2),$W$2:$X$6,2,1)

then editing the formula, selecting only the $W$2:$X$6 bit and pressing F9 on the keyboard,
this will produce the likes of:=VLOOKUP(TODAY()-MAXIFS($H$2:$H$1935,$B$2:$B$1935,B2),{-1,"0-30";30,"30-60";60,"60-90";90,"90-365";365,">365"},2,1),
then pressing Enter. Then you can copy down, and delete the lookup table.

Oops, I've just noticed you want the oldest date kept, so change MAXIFS to MINIFS.

[SamT]

A single "Macro" would have to loop thru the entire list every time a change is made.

IMO, you would be better served with a Vendor Table that showed the Aging Date for each Vendor. Most times, only a VLookup would be needed when a new entry is made to the main sheet. I am sure you will find other uses for a Vendor Table

While it will take some "run Once" procedures and several minutes of Run Time, AND, some time to fully update the Main sheet the first time, IMO, it would save much time in Production as only New entries would use any Code.

Some issues I see are the each Row in the Vendor table that were not >365 still need to be updated Daily. This can be a fast simple Procedure if the oldest RO Create Date is also stored in the Vendor Table.

If you decide to use this method, Start by copying the Main Sheet, then delete all columns EXCEPT Vendor and RO Create Date, Sort both Ascending (Vendor then Date). Use a simple procedure to delete all but the first Row for each vendor.

I prefer Raw data when possible, (the Conversion to the strings you want in the main sheets can happen anywhere,) so I would use the following UDF for the (New) RO_Age column of the Vendors Table

Function AgingDays(ROCreateDate As Range) As Long
'Returns the number of days between the ROCreateDate and today.
    AgingDays = DateDiff("d", ROCreateDate, Date)
End Function

Because that Function is not Volatile, you will want this in the ThisWorkbook Code page

Private Sub Workbook_Open()
    Sheets("Vendor Table").Range(RO_Age_Column_Address).Calculate
End Sub

[tonyk1051]

Hi,

The formulas does work and like you said it does need a bit tweaking as some dates didnt match up. What do the numbers in w2 reference? and how do i read the table in column T/U...i just need a quick dummy tutorial as im on a tight schedule... apprectiate the help

[tonyk1051]

Hi Sam,

You are talking to a excel noobie here, so i can barley process what you just said about the macro, P45 methods seems more easier, i just need to understand what the numbers he puts in the table means/is referencing. Sorry for being difficult...when i need to learn something new it has to be super simplified for a guy like me....

[p45cal]

it does need a bit tweaking as some dates didnt match up.

Be specific: which dates? Best attach your workbook and highlight which are wrong and what you think it should be.
It's night time here so won't be looking again for at least 8 hours

[tonyk1051]

Hi,

Nope I was wrong, its right. If I didnt want the oldest date kept and just wanted wanted it regular (each RO create date falls into the catergory i assigned) then I keep it at MAXIFS?

[p45cal]

For each row to look only at itself in I2 or P2:

=VLOOKUP(TODAY()-H2,{-1,"0-30";30,"30-60";60,"60-90";90,"90-365";365,">365"},2,1)