I Need the exact difference between to dates, Birth and Death

[Asgard221]

When I put the formula into the test spreadsheet the attached is what it looks like, when I put the formula into the actual spread sheet I am using (A test sheet was made because I did not want to screw up 4400 lines of information) I get 0 years 0 month/s zero day/s

[sandy666]

try

DATEDIF() function

	H	I
2	84 years 2 month/s 16 day/s	=DATEDIF(D2,E2,"Y")&" years "&DATEDIF(D2,E2,"YM")&" month/s "&ABS(DATEDIF(D2,E2,"MD"))&" day/s"
3	84 years 1 month/s 8 day/s

[alansidman]

tutorial https://exceljet.net/formulas/get-da...-between-dates

[FlameRetired]

Similar to sandy666 but shorter: enter this formula in F2 and copy down.

=DATEDIF(D7,E7,{"y","ym","md"})&{" years"," months"," days"}

	D	E	F	G	H
1	Birth	Death	Age
2	10/06/1938	12/22/2022	84 years	2 months	16 days
3	11/12/1938	12/20/2022	84 years	1 months	8 days

[Asgard221]

That you for they reply, It does not work either, but it may be me. How do you have the cells in D2 AND D3 AND E2 AND E3 formatted?

[sandy666]

your dates are text not a dates
re-enter them and try again

textdate.png

normally dates are right-aligned automatically because date is a number

[Asgard221]

Thank you sandy666, I have changed all cells to date (Long date), I have the same result as before.

[sandy666]

as I said, RE-ENTER dates

[Asgard221]

OK, I have re-entered the dates (I take it you mean to re type them into the boxes now that it says date) I did not seem to make a difference.

[sandy666]

what kind of date format are you using?

dd/mm/yyyy or mm/dd/yyyy

use the same as your system date is

22/12/2022

12/20/2022

as you can see one is date and the second is a text (a year has only 12 months )

Birth	Death	Age
10/06/1938	12/22/2022	#VALUE!
11/12/1938	12/20/2022	#VALUE!
06/10/1938	22/12/2022	84 years 2 month/s 16 day/s
12/11/1938	20/12/2022	84 years 1 month/s 8 day/s

[Asgard221]

Well, now that may be part of the problem, I have entered them as the system date format and it works, except both lines are the exact same answer. As per attached.

[sandy666]

it seems to be ok

and DON"T USE ALIGNING before you end your work !!!

datealign.png

you should be more carefully not only copy/paste

reference.png

[Asgard221]

I was just typing you back to say I seen that. I fixed it and it works, can you tell me why it is giving me 2 different date displays. as highlighted?

[sandy666]

can you tell me why it is giving me 2 different date displays. as highlighted?

I don't see any highlighted cells

[Asgard221]

September 7, 1954
February 14, 1955
February 14, 1954
December 28, 1954
December 28, 1955
1954-02-14
1955-02-14
1955-02-14

I probably did not do it correctly, but this cut and paste shows it.

[Asgard221]

I figured that one out as well.
I thank you for you help sandy666

[sandy666]

dates are not the same here

diffdates.png

[sandy666]

You are welcome

I just wanted to give up

if the problem is solved, mark the thread as SOLVED (top above your first post - Thread Tools)

[Asgard221]

NEVER give up. This is only the second time that I asked for help, and the other time was a few years ago. Where do I place the "SOLVED"

[sandy666]

scroll up and on the right side you should see Thread Tools

tt.png