URGENT - how do you do simple sums with cells set to date format

[Muzza86]

e.g.


cell a1 = 19/11/1944
cell a2 = =TODAY()

how do I put a formula in cell a3 to calculate the difference between the two dates

e.g.


above example answer would be = 70

[romperstomper]

=A2-A1
and make sure you format the formula cell as a number.

Edit: should have read more closely as your question doesn't match your title. If you want the year difference you can use:
=YEAR(A2)-YEAR(A1)
You can also use DATEDIF if you want complete years elapsed between the two:
=DATEDIF(A1,A2,"Y")
but that will return 69, not 70.

[Fotis1991]

Please Login or Register  to view this content.

[Muzza86]

im trying that but its not working. its strange.

does it matter that my cells and dates are set to custom format of mm/dd/yyyy

also I have several start dates of employee's . and im trying to do a sum to calculate how long they have been at the company. so I need to use =TODAY() into a cell

e.g. something like =TODAY()-year(a1) ??

[romperstomper]

Define "not working".

[Muzza86]

I have a column of cells with dates custom formatted to mm/dd/yyyy


I have put a cell elsewhere on sheet with the =TODAY() formula


e.g. exact example of whats happening... c5 = 01/03/1988 e8 = =TODAY()


my formula in d5 is =year(e8)-year(c5)

it shows.... 01/26/1990


I need it to show... 26 years

[romperstomper]

As I said originally - format the formula cell as a number, not a date.

[Muzza86]

oh thanks, yeah stupid of me.works

[sktneer]

Dates in Excel are actually stored as serial numbers. Where 01/01/1900 has the serial number 1. To see this, input 1 in any cell in the sheet and format that cell as Date and the cell will show you now 01/01/1900. That is because excel is converting serial number to a date which is 01/01/1900. Same way if you input 26 in a cell and format it as a Date, it will show you 01/26/1900 (mm/dd/yyyy).
So the formula is calculating correctly but different cell format will show you different output. Therefore as romperstomper said, format the formula cell as Number and it will show you the desired output.

Hope this helps.