# Working out a date length in another cell

#### LeeBillington

##### Board Regular
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")

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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

#### T. Valko

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member

Excel 2010
BCDEF
11 Years, 0 Months, 1 Days
21 Years, 0 Months, 1 Days
3
4The date appears to be TEXT; try formula shown in B2.
151-Jan-16
1601 January 2016
1d
Cell Formulas
RangeFormula
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")

Replies
2
Views
519
Replies
2
Views
3K
Replies
18
Views
1K
Replies
9
Views
399
Replies
7
Views
1K

1,191,174
Messages
5,985,106
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back