Date Calculation Help

KuraiChikara

Board Regular
Joined
Nov 16, 2016
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
Hello,
I have a simple concept that I can't seem to figure out.
I'm trying to simplify a process on how many years have gone by, from a date of purchase of a product, compared to the current date.

Example of a working process: I purchase a server on 03/21/2014, that's RAW data. I can then manually enter todays date 11/17/2020 in another cell (or use a formula), subtract todays date from date of purchase then divide by 365. The end result is correct but I can only figure this process out by having 4 columns when I only want two.
Date of PurchaseManual Entry Todays DateSubtractionDivision
03/21/201411/17/20202433 (days)6.67 (years)


I want to simplify using only two columns; the Date of Purchase column and a column for the formula but I can't figure that out.

I plug in the following formula, which I think mimics the same steps as above, but it doesnt work: =TODAY()-A2/365, in NUMBER format it gives me 44037.70 as a result.
How can I reflect the answer of 6.67 years?

Date of PurchaseFormula
03/21/2014=TODAY()-A2/365
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=(TODAY()-A2)/365
or
Excel Formula:
=YEARFRAC(A2,TODAY())
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,521
Office Version
  1. 365
Platform
  1. Windows
Order of operations is important here. You need parentheses:
Excel Formula:
=(TODAY()-A2)/365
 

KuraiChikara

Board Regular
Joined
Nov 16, 2016
Messages
102
Office Version
  1. 2013
Platform
  1. Windows
Order of operations is important here. You need parentheses:
Excel Formula:
=(TODAY()-A2)/365
Thank you, I know it had to do with the Order of Operations, I just didnt know how to set it up that way.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,224
Messages
5,576,830
Members
412,749
Latest member
BlakeVanderMeer
Top