VBA to calculate aging of workorders

[fvisions]

Hello,
I have a spreadsheet of open workorder and am looking to create an aging table to alert staff when the order is over 2 weeks and still open, then 4 weeks, then 60 and 90+days from the date of creation.
Col A W/O# | Date Created | Date Planned | Date Completed | Aging Alert
1030111123 12/1/2024 1/27/2025 (todays date 3/11/25) >90days
1030235443 3/1/2025 4/1/2025 (todays date 3/11/25) <14 days

 

[Sanjeev1976]

Hi,

You can check the below option :

Book2 (version 1).xlsb
	A	B	C	D	E
1	Col A W/O#	Date Created	Date Planned	Date Completed	Aging Alert
2	1030111123	01/12/2024	27/01/2025	11/03/2025	> 4 Weeks
3	1030235443	01/03/2025	01/04/2025	11/03/2025	In time
Sheet4

Cell Formulas
Range	Formula
E2:E3	E2	=LET(duration,D2-C2+1,IFS(duration>90,"> 90 days",duration>60,"> 60 days",duration>28,"> 4 Weeks",duration>14,"> 2 Weeks",duration<=14,"In time"))

 

[fvisions]

Hi,

You can check the below option :

Book2 (version 1).xlsb
	A	B	C	D	E
1	Col A W/O#	Date Created	Date Planned	Date Completed	Aging Alert
2	1030111123	01/12/2024	27/01/2025	11/03/2025	> 4 Weeks
3	1030235443	01/03/2025	01/04/2025	11/03/2025	In time
Sheet4

Cell Formulas
Range	Formula
E2:E3	E2	=LET(duration,D2-C2+1,IFS(duration>90,"> 90 days",duration>60,"> 60 days",duration>28,"> 4 Weeks",duration>14,"> 2 Weeks",duration<=14,"In time"))

Is this the VBA code I can copy and run? My boss changed to wanting one column per bucket so for <2weeks column S, <30 days Column T etc. how can I adjust for this change?

 

[Sanjeev1976]

Based on your office version, this was a dynamic formula and not a VBA. You can share a small sample for your requirement stating the required results and me or someone else will provide you with a formula.

 

[fvisions]

Ok so I tried modifing the formula to:

LET(duration, (TODAY()-$O2)+1,IFS(duration>90,">90 Days",duration>60,"> 60 Days",duration>30,"> 30 Days",duration<=30,"In time", duration<=7,"Current"))

Col O is the creation date the other dates do not matter for the aging buckets. Example of what I am looking for:

 

[Sanjeev1976]

You can try the below :

Book3
	O	P	Q	R	S	T	U				Y	Z	AA	AB
1	Created On	Created	Planning	Changed On	Changed By	Plant for WorkCent	City				1-30	31-60	61-90	Over 90
2	06/03/2025	RFCUSAISU	9310	06/03/2025	U436972	9310	BETHEL				In time
3	14/02/2025	RFCUSAISU	9310	21/02/2025	IJ354863	9310	HIRAM					> 30 Days
4	13/02/2025	RFCUSAISU	9310	16/02/2025	137422	9310	GORHAM					> 30 Days
5	15/01/2025	RFCUSAISU	9310	04/02/2025	58458	9310	LIMERICK						> 60 Days
6	17/12/2024	RFCUSAISU	9310	04/02/2025	1-1354863	9310	PARSONSFIELD							> 90 Days
Sheet1

Cell Formulas
Range	Formula
Y2:AB6	Y2	=IFERROR(LET(result,LET(duration,(TODAY()-$O2)+1,IFS(duration>90,"> 90 Days",duration>60,"> 60 Days",duration>30,"> 30 Days",duration<=30,"In time",duration<=7,"Current")),IFS(AND(result="In time",COLUMNS($Y$1:Y$1)=1),"In time",AND(result="> 30 Days",COLUMNS($Y$1:Y$1)=2),"> 30 Days",AND(result="> 60 Days",COLUMNS($Y$1:Y$1)=3),"> 60 Days",AND(result="> 90 Days",COLUMNS($Y$1:Y$1)=4),"> 90 Days")),"")

 

[fvisions]

Can i copy the formula to VBA? I am running VBA with command buttons as I am not the only employee that will utilitze this report.
Thank you for all your help.

 

[Sam_D_Ben]

@Sanjeev1976 sorry to intervene,
@fvisions , Please try this,

Sub Update_Aging()
    Dim ws As Worksheet, lastRow As Long, i As Long, j As Long, dur As Long
    Dim category As String, ageCols As Variant
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "O").End(xlUp).Row
    ageCols = Array("Current", "In time", "> 30 Days", "> 60 Days", "> 90 Days")

    For i = 2 To lastRow
        dur = IIf(IsDate(ws.Cells(i, 15)), Date - ws.Cells(i, 15) + 1, 0)
        category = Choose(IIf(dur <= 7, 1, IIf(dur <= 30, 2, _
                    IIf(dur <= 60, 3, IIf(dur <= 90, 4, 5)))), _
                    ageCols(0), ageCols(1), ageCols(2), ageCols(3), ageCols(4))

        For j = 25 To 28
            ws.Cells(i, j).Value = IIf((j - 24) = _
                IIf(category = "Current", 1, IIf(category = "In time", 1, _
                IIf(category = "> 30 Days", 2, IIf(category = "> 60 Days", 3, 4)))), category, "")
        Next j
    Next i
    
End Sub

 

[fvisions]

This is perfect! Thank you, Thank you, Thank you.

 

[Sam_D_Ben]

This is perfect! Thank you, Thank you, Thank you.

No problem! Happy to help.