• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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.
0
capterdi
Asked:
capterdi
  • 2
  • 2
1 Solution
 
byundtCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now