Working out a date length in another cell

[LeeBillington]

basically i want the date in that cell to be worked out in another cell of the exact length.
i've copied this formula from somewhere and slightly changed it but all im getting is #VALUE

=IF(B13="","",DATEDIF(B13,J1,"Y")&" Years, "&DATEDIF(B13,J1,"YM")&" Months, "&DATEDIF(B13,J1,"MD")&" Days")

 

[FDibbins]

What's in B13?
What's in J1?

 

[T. Valko]

The #VALUE! error typically means that one of the referenced dates is not a true Excel date.

Are the dates in B13 and J1 manually entered? If so, try re-entering them.

 

[LeeBillington]

B13 Is A start date.. for example "01 January 2016" and J1 Is todays date. so i want it to work out the length of time between todays date and the start date.

 

[T. Valko]

B13 Is A start date.. for example "01 January 2016" and J1 Is todays date. so i want it to work out the length of time between todays date and the start date.

What results do you get from these formulas:

=ISNUMBER(B13)

=ISNUMBER(J1)

If either one returns FALSE then whatever is entered in that cell is NOT a true Excel date.

 

[FDibbins]

As Tony suggested, maybe 1 cell contains text rather than a real date.

test with =isnumber(B13) FALSE indicates text (and what you say it contains also indicates it may be text), we will need to convert it for you

 

[Dave Patton]

Excel 2010
	B	C	D	E	F
1	1 Years, 0 Months, 1 Days
2	1 Years, 0 Months, 1 Days
3
4	The date appears to be TEXT; try formula shown in B2.
15	1-Jan-16
16	01 January 2016
1d

Cell Formulas
Range	Formula
B1	=IF(B15="","",DATEDIF(B15,J1,"Y")&" Years, "&DATEDIF(B15,J1,"YM")&" Months, "&DATEDIF(B15,J1,"MD")&" Days")
B2	=IF(B16="","",DATEDIF(B16+0,J1,"Y")&" Years, "&DATEDIF(B16+0,J1,"YM")&" Months, "&DATEDIF(B16+0,J1,"MD")&" Days")