How to Group Dates into 30-Day Age Ranges Using Excel Formulas
Question details
The user needs an Excel formula to group records into age ranges based on the number of days since a specific date, using exact 30-day increments instead of rounded-up values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing or aging records such as unpaid invoices or open tickets into 30-day buckets based on a starting date.
- Observed behavior
- Requires a formula that outputs text labels like 'Over 30 days old' or 'Over 60 days old' based on completed 30-day periods.
Verify that your start dates are formatted as valid Date values in Excel, and identify if there is a condition cell (such as a completion date) that needs to be blank before the calculation triggers.
Use the FLOOR and TODAY Functions to Calculate 30-Day Increments
Combine the IF, FLOOR, and TODAY functions to calculate the exact number of completed 30-day periods and generate a dynamic text label.
The FLOOR function rounds a number down to the nearest specified multiple (in this case, 30). By subtracting the start date from today's date, we get the total elapsed days. FLOOR buckets these days into exact 30-day increments.
Click on the cell where you want the age range label to be displayed.
Type the formula =IF(D2="","Over "&FLOOR(TODAY()-C2,30)&" days old","") into the formula bar. In this example, C2 is the start date and D2 is a condition cell (like a closing date).
Press Enter to see the result, then click and drag the fill handle at the bottom-right of the cell down to apply this formula to the rest of your data.
Easily Manage Date Calculations in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, including FLOOR, TODAY, and complex IF statements. You can seamlessly bucket your data into custom age ranges to track invoices, inventory, or project tasks.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the date records.
- 2. Input the formula: Select your output cell and enter =IF(D2="","Over "&FLOOR(TODAY()-C2,30)&" days old","").
- 3. Drag to fill: Press Enter to view the output label, and use the drag-fill handle to copy the formula down your column.
Frequently Asked Questions
How can I change the formula to group by 15-day or 60-day increments?
Simply modify the significance argument in the FLOOR function. For 15-day increments, change the 30 to 15, resulting in =IF(D2="","Over "&FLOOR(TODAY()-C2,15)&" days old","").
Why is my formula returning a '#VALUE!' error?
This usually occurs if your start date in cell C2 is stored as text rather than a valid date. Select the cell, go to the Home tab, and change the Number Format dropdown to 'Short Date'.
Can I use a specific end date instead of today's date?
Yes. If you have a specific end date stored in another cell, such as E2, replace the TODAY() function with E2. The updated formula will be =IF(D2="","Over "&FLOOR(E2-C2,30)&" days old","").
How do I remove the blank cell condition from the formula?
If you want to calculate the age range for every row regardless of another cell's status, remove the IF statement and use just the core calculation: ="Over "&FLOOR(TODAY()-C2,30)&" days old".
