Subtracting two dates

[jewellove]

How will i format 31.12.13 to date of dd/mm/yy.

I can't subtract 31/04/14 from 30/03/14 or 31.04.14 from 30/03/14.

[José Augusto]

Send a file to analyze what you want.

[José Augusto]

If G6 contains the date (text) "31.12.13" you can convert it using the following formula

Please Login or Register  to view this content.

[Shareez Saleem]

Formatting option is shown in the attached sheet.

Assume that the date (which excel not treat as date) is in A2.
Formula: =DATE(2013,MID(A2,4,2),LEFT(A2,2))

13 is will consider as 1913 while doing the Right option in date Function, so better to right it manually in the formula.

Regards
Shareez

[FDibbins]

How will i format 31.12.13 to date of dd/mm/yy.

quick answer, you cannot. That is a text entry, not a date, you need to 1st convert it to a real date using a formula or function. Then you can format it as needed

I can't subtract 31/04/14 from 30/03/14 or 31.04.14 from 30/03/14.

Again, because those are text and not real dates, you cannot to any math on them until they are converted to dates.

What you need to understand about dates and times in excel is that a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu Jun 2015) is actually 42159

The suggested formulas should do what you need for the conversion, you should then be able to calc the difference from that