date

[rathanak]

Hi any one knows how to calculate difference between 2 dates prior to 1900 for eg difference between - 15/10/1888 to 22/06/2016

[snasui]

Go to Excel Options (File > Options or Alt+F, T) and set date to 1904 date system. See picture below.

[ratanak]

After that can we use datedif to do calculation?

[snasui]

Yes, You can use all functions that can calculate numeric.

[ratanak]

Please provide formula

[snasui]

Please attatch file and enter your correct result.

[ratanak]

i.e 15/10/1888 to 22/06/2016

[snasui]

Assumming your data in A1:B1.

C1 Enter this formula.

=B1-A1

[ratanak]

If i want years ,months and days between these two dates?

[snasui]

I don't know your exact result value. Please tell me each correct results.

You can devide with 365 for year, devide with 12 for month and again please attach file.

[rathanak]

Can this output is possible in one cell like 128 year 8 month & 7 days?

[snasui]

Set Excel option to default, then try this,

=DATEDIF(LEFT(A2,6)&RIGHT(A2,4)+2000,LEFT(B2,6)&RIGHT(B2,4)+2000,"y")&" Years "&DATEDIF(LEFT(A2,6)&RIGHT(A2,4)+2000,LEFT(B2,6)&RIGHT(B2,4)+2000,"ym")&" Months "&DATEDIF(LEFT(A2,6)&RIGHT(A2,4)+2000,LEFT(B2,6)&RIGHT(B2,4)+2000,"md")& " Days"

!	Note: Enter date with text format, dd/mm/yyyy (10 digits)

[rathanak]

It is still not working

[logic]

Why not attach file?

[ratanak]

I have solution by using VBA frm https://support.microsoft.com/en-us/kb/245104