Subtraction of date

[Sandip Shah]

Hi,

We have a database where in the date is mentioned in MMYY format.

For eg.

1098 - Represents Month October and Year 1998
0302 - Represents Month March and Year 2002

I need to arrive at the number of months from 1098 to 1203 (Current
month). Any ideas on how i should go forward.

0302 Minus 1203 = 18 months.

Regards
Sandip.

 

[Arvi Laanemets]

Hi

=DATEDIF(DATE(MID(A1,3,2)+IF(MID(A1,3,2)*1<5,100,0),MID(A1,1,2),1),DATE(MID(
B1,3,2)+IF(MID(B1,3,2)*1<5,100,0),MID(B1,1,2),1),"M")

 

[Ron Rosenfeld]

Hi,

We have a database where in the date is mentioned in MMYY format.

For eg.

1098 - Represents Month October and Year 1998
0302 - Represents Month March and Year 2002

I need to arrive at the number of months from 1098 to 1203 (Current
month). Any ideas on how i should go forward.

0302 Minus 1203 = 18 months.

Regards
Sandip.

=(RIGHT(A2,2)+100*(--RIGHT(A2,2)<30)-
RIGHT(A1,2)-100*(--RIGHT(A1,2)<30))*12+
INT(A2/100)-INT(A1/100)

or, if it will always be from the current month:


=DATEDIF(DATE(MOD(A1/100,1)*100+1900+100*(MOD(A1/100,1)*100<30),
INT(A1/100),1),TODAY()-DAY(TODAY())+1,"m")


--ron

 

[Dave Peterson]

And one more if your windows short date were in mdy order:

=DATEDIF(DATEVALUE((TEXT(A1,"##""/01/""##"))),
DATEVALUE((TEXT(A2,"##""/01/""##"))),"m")
(one cell)

But how did you get 18 months for the difference? I got 41.