Excel: Use IF Function to Calculate Age Buckets


You can use the following basic syntax in Excel to use the IF function to calculate age buckets:

=IF(C2<=40,"1-40 Days",IF(C2<=80,"41-80 Days",IF(C2<=120,"81-120 Days",">120 Days")))

This particular formula looks at the number of days in cell C2 and returns the following values:

  • 1-40 Days if the value in cell C2 is less than or equal to 40
  • Else, 41-80 Days if the value in cell C2 is less than or equal to 80
  • Else 81-120 Days if the value in cell C2 is less than or equal to 120
  • Else, >120 Days

Note that this particular formula creates four distinct age buckets, but you can use as many nested IF functions as you’d like to create even more age buckets.

The following example shows how to use this formula in practice.

Example: Use IF Function to Calculate Age Buckets in Excel

Suppose we have the following dataset in Excel that shows when various employees started at some company:

Suppose we would like to classify each employee into an age bucket based on how long they have been with the company.

We can first calculate the number of days they have been with the company by typing the following formula into cell C2:

=DATEDIF(B2, TODAY(), "d")

We can then click and drag this formula down to each remaining cell in column C:

Column C now shows the number of days that each employee has been with the company.

Note: We used the DATEDIF() function to calculate the date difference (“d” = in days) between the start date in column B and the current date.

This article was written on 6/23/2023, which was used as the current date in this particular formula.

Next, we can type the following formula into cell D2 to calculate the age bucket for each employee:

=IF(C2<=40,"1-40 Days",IF(C2<=80,"41-80 Days",IF(C2<=120,"81-120 Days",">120 Days")))

We can then click and drag this formula down to each remaining cell in column D:

Excel IF function to calculate age brackets

Column D now classifies each employee into one of four distinct age buckets based on the number of days they have been with the company.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use IF Function with Negative Numbers
Excel: How to Use a Formula for “If Not Empty”
Excel: How to Use a RANK IF Formula

Leave a Reply

Your email address will not be published. Required fields are marked *