help with Expression

[arusanah]

I have two Variables vFromDate & vToDate.  My requirement is to find num of months between two variables . I'm writing  expression

=(num(month(vToDate))-num(month(vFromDate)))+1. which works fine as long as year is same . e.g 1/1/2014 - 7/31/2014 . but the minute i change date to different yr e.g 1/1/2013 - 7/31/2014 .. the above expression fails .

I tried another expression

=num(Date(vToDate))-num(Date(vFromDate))+1

this gives me no of days . so now i need to convert those back to no of months

e.g total no of days is 577 and if converted to month should give me 19 months .

pls help!

[swuehl]

Maybe something like this will help you:

SET DateFormat='DD/MM/YYYY';


INPUT:
LOAD *
, if(day(VisitDay) >= day(DoB),
  mod(MonthIDVisitDay-MonthIDDoB,12),
  if(mod(MonthIDVisitDay-MonthIDDoB,12)=0,11,mod(MonthIDVisitDay-MonthIDDoB,12)-1)) as AgeMonths
, if(day(VisitDay) >= day(DoB),
  day(VisitDay)-day(DoB),
  VisitDay-addmonths(DoB,12*AgeYears+if(mod(MonthIDVisitDay-MonthIDDoB,12)=0,11,mod(MonthIDVisitDay-MonthIDDoB,12)-1))) as AgeDays
;
LOAD *
, age(VisitDay,DoB) as AgeYears
, Year(DoB)*12+Month(DoB) as MonthIDDoB
, Year(VisitDay)*12+Month(VisitDay) as MonthIDVisitDay
INLINE [
DoB, VisitDay
01/01/2011, 11/03/2012
01/01/2010, 29/02/2012
01/03/2010, 29/02/2012
31/01/2011, 01/02/2011
28/02/2000, 01/03/2012
28/02/2000, 01/03/2011
29/02/2008, 19/02/2012
14/05/2012, 02/09/2013
31/01/2013, 30/01/2014
];

[swuehl · Accepted solution]

An approximation to month difference could also be achieved by

=(vToDate- vFromDate+1) / 30.5

[stigchel]

You can calculate the difference in months like this:

=Num(((year(vToDate) * 12) + month(vToDate)) - (((year(vFromDate) * 12) + month(vFromDate))) + 1)

[pratap6699]

=num(Month(Vtodate))-num(Month(Vfromdate))

[arusanah]

Thank you so much Swuehl,

Simple , short And perfect  solution

[arusanah]

Thanks Piet ,

Your Expression was perfect too .. unfortunately i can mark only one as correct answer .. But thank you so much for your help!!

[stigchel]

Glad to be of help