Excel date arithmetic problem

November 14, 2008 9:55 AM

MS Excel Question: If I have a column with a range of dates in it, is there a formula that will tell me how many years ago that date was from today?

I am trying to calculate employee discount levels. For every year of service, an employee gains an additional percentage discount. I have employee hire dates in an Excel spreadsheet, and I would like to be able to, at a glance, determine what discount level a given employee is at, even if I open the spreadsheet three years from now. The levels top out at five years, if that matters.

posted by Rock Steady to computers & internet (13 answers total)

=IF((TODAY()-A1)/365>=5,5,(TODAY()-A1))

Where A1 is the column with the initial dates in it.
posted by fuzzbean at 10:12 AM on November 14, 2008

Er, whoops. Left a bit out.

=IF((TODAY()-A1)/365>=5,5,((TODAY()-A1)/365))
posted by fuzzbean at 10:13 AM on November 14, 2008

=YEAR(NOW()-A1)-1900

Again, cell A1 contains the desired past date. Format this output cell with a non-date number format (like "General" or "0").
posted by IAmBroom at 10:16 AM on November 14, 2008

Sure, the "TODAY" function will give you today's date, and excell handles date subtraction as you would expect so the formula "=TODAY()-DATE(2008,11,13)" would return "1". Just do the subtraction and divide by 365. If you want to use 360 day years (12 30 day months) you can yse the DAYS360 function which will return the days between dates based on 360 day years.

What format are the dates in? You can use the "DATEVALUE" function to convert them to excell format dates before subtracting -- oh and then just divide by 365 to get years, of course.
posted by The Bellman at 10:17 AM on November 14, 2008

Thanks for the update, fuzzbean. I was wondering what possessed you...
posted by IAmBroom at 10:17 AM on November 14, 2008

Or, what fuzzbean said, as long as A1 is in excell date format.
posted by The Bellman at 10:17 AM on November 14, 2008

Hey, that's great fuzzbean. Can you explicate the formula a little for me? I'd like to understand why it works, if I can.
posted by Rock Steady at 10:19 AM on November 14, 2008

Nothing personal against fuzzbean (since I already jibed him about his code error), but the formula I posted is shorter, clearer, and immune to leap year errors.

If you want the 5-year max included, use:

=MIN(YEAR(NOW()-A1)-1900, 5)
posted by IAmBroom at 10:26 AM on November 14, 2008

Thanks to you, too, IAmBroom.
posted by Rock Steady at 10:32 AM on November 14, 2008

The Bellman's got it pretty much covered but here:

=TODAY() returns today's date.
TODAY()-A1 returns the number of days between today and the employee's start date.
(TODAY()-A1)/365 converts that to years.

Then it's nested in an IF function, which say basically, if condition A is met, return X, if condition A is not met, return Y.
So in =IF(A1=4,1,0) A1=4 is condition A. A1=4 is our condition A; 1 is our X; 0 is our Y. If the value in A1 is 4, the formula will return 1. If the value is anything other than 4, it will return 0.

=IF((TODAY()-A1)/365>=5,5,((TODAY()-A1)/365)), the bolded italic part (TODAY()-A1)/365>=5 is condition A.
=IF((TODAY()-A1)/365>=5,5,((TODAY()-A1)/365)), 5 is X.
=IF((TODAY()-A1)/365>=5,5,((TODAY()-A1)/365)), ((TODAY()-A1)/365) is Y.

So in English, this says, if the number of years between A1 and today is greater than or equal to 5, give me a 5, if it's less than 5, give me the number of years between A1 and today. Make sense?

On preview, I'm a her. :D
posted by fuzzbean at 10:34 AM on November 14, 2008

I'm not fuzzbean, but I do want to take a break from work:

=IF((TODAY()-A1)/365>=5,5,((TODAY()-A1)/365))

TODAY()-A1 = number of days between today, and the date you're checking.
Divide that by 365 to get the number of years between the dates.

The IF statement says "If the number of years between the dates is greater than or equal to 5 years, then just say it's 5 years apart. Otherwise, calculate the actual number of years apart and use that."

But yeah, I would probably go with IAmBroom's because it avoids leap year issues.
posted by inigo2 at 10:34 AM on November 14, 2008

The magic here is in how dates are stored. They're actually stored as a cardinal number of days since the epoch (i.e. "the start of time" which in this case turns out to be 1900). Formatting a cell as "date" makes Excel convert that number into a year/month/day for display purposes.

But you can treat that value as a number, and do math on it. Subtracting one date from another gives you the number of days between them.
posted by Class Goat at 10:55 AM on November 14, 2008

I'm a big fan of DATEDIF in cases like this, so I'd use:

=MIN(DATEDIF(A1,TODAY(),"y"),5)

The "y" indicates that you want the results in whole years. You can use "d" or "m" for days or months, and if you want to get really tricky and exclude weekends and holidays you can use NETWORKDAYS.
posted by stefanie at 12:17 PM on November 14, 2008