Help with Aging Inventory Buckets

[Shadyguy904]

I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:

RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days

I am using the following formula:

=IF(D2="","N/A",IF(TODAY()-D2<-25,"RCC",IF(AND(TODAY()-D2>-26,TODAY()-D2<-11),"0 - 14 days",IF(AND(TODAY()-D2>-12,TODAY()-D2<-4),"15 - 21 days",IF(AND(TODAY()-D2>-5,TODAY()-D2<1),"22 - 30 days",IF(TODAY()-D2>0,"31 + days"))))))

This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?

The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...

Any help is greatly appreciated.

 

[dcamos]

What exactly in the formula do you have to change every day? I assume a lookup table would be the best for this but I need a little more information.

 

[Shadyguy904]

I have to change the amounts because some of the dates are no longer in the appropriate bucket when using the same formula.

Here is an example:

=IF(AND(TODAY()-D2>-26,TODAY()-D2<-11),"0 - 14 days",

Just isolated this protion of the formula for illustrative purposes

The next day the formula would be tweaked slightly to this:

=IF(AND(TODAY()-D2>-24,TODAY()-D2<-12),"0 - 14 days",

I change it because the dates appear in the wrong bucket based on the original formula. If I have only two days left for this week only a few values (based on the date range) would apply. But if I check the report on a Monday, there would be a greater range of dates required.

Thanks for the quick response... I hope this helps

 

[barry houdini]

It's hard for me to tell how the formula should change. Are you effectively counting within Sunday to Saturday weeks? Can you give some sample dates and define which categories they would fall in to today......and when those categories would change (do the categories only change when you move from Sat to Sun?)

To start with you could simplify your formula using LOOKUP, i.e. something like this

=IF(D2="","N/A",LOOKUP(TODAY()-D2,{-999999,-25,-11,-4,3},{"RCC","0 - 14 days","15 - 21 days","22 - 30 days","31 + days"}))

.......now you probably need to change TODAY() to something that will return the next Sunday.......

 

[Shadyguy904]

Yes, thats correct I am working within Sunday to Saturday weeks...

Here are some examples of the dates (based on today's date 8/27):

Date Bucket

08/26 +Earlier 31+
08/27-08/29 23-30
08/30-09/05 15-22
09/06-09/19 0-14
09/22 +Later RCC

Thanks! HTH

 

[Shadyguy904]

Date - Bucket

(08/26 +Earlier) - 31+
(08/27-08/29) - 23-30
(08/30-09/05) - 15-22
(09/06-09/19) - 0-14
(09/22 +Later) - RCC


The formatting got a little screwy... Tried to clean it up for easy reading.

 

[swat2]

I would use a Select Case in a user defined function.

@ Barry: Will this update daily?

Public Function InventoryAge(DueDate As Date) As String

Select Case DueDate

Case Is < DateValue(Now())
    InventoryAge = "31+ Days"
    
Case Is <= DateValue(Now()) + 14
    InventoryAge = "0-14 Days"
   
Case Is <= DateValue(Now()) + 22
    InventoryAge = "15-22 Days"
  
Case Is <= DateValue(Now()) + 30
    InventoryAge = "23-30 Days"

Case Else
    InventoryAge = "Not Categorized"
    
End Select

End Function

 

[barry houdini]

I think you could update my formula suggestion like this:

=IF(D2="","N/A",IF(D2< A$1,"31 + days",LOOKUP(D2-A$1-8+WEEKDAY(A$1),{-7,0,7,21},{"23 - 30 days","15 - 22 days","0 - 14 days","RCC"})))

where A1 contains todays' date.

Categories should update for this week's dates as the week progresses but dates in the next 4 weeks will only change come Sunday. I suggest you try using different dates in A1 to test.....

 

[Shadyguy904]

Cheers mates! I will check the formula upon my return to work. I got a short reprieve

 

[Shadyguy904]

Thanks for all the help! Everything works great...

MrExcel FTW!!