Calculating with custom date format.

Hi EE,

Help needed here. I use imported data where dates are expressed in this format:

yy.mm.dd

Example: 12.03.30

And I have pair of data, where I need to get the days between dates.

Example:

A1 = 12.03.12
A2 = 12.03.30

A2 - A1 = 18 days.

How to compute this in Excel?

Thanks.
capterdiAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
In US Excel with dates all in year 2000 or later, the following formula works:
=SUBSTITUTE("20" & A2,".","/")-SUBSTITUTE("20" & A1,".","/")
0
 
capterdiAuthor Commented:
Excellent.

Thanks.
0
 
byundtCommented:
I wasn't sure if the formula would be working in Mexico--because you might be using a different default date format (dd/mm/yy).

The following formula should always work:
=DATE(LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2))-DATE(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))
0
 
capterdiAuthor Commented:
Both formulas have worked OK. I use english version of Excel.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.