Calculate number of months between dates in script

[agaetisproject]

Hi there,

Anyone can help me compute the number of months between two dates in the script.

It seems quite obvious but I can't manage to make this work so would appreciate your guidance.

Below an example of what I try to achieve:

- Start Date and End date are part of the original file

- NumberOfMonth is what I try to compute.

Many thanks for your helps!

Start Date	End Date	NumberOfMonth
01/01/2016	31/12/2016	12
01/01/2016	31/07/2016	7
01/01/2016	15/08/2016	8
01/01/2016	28/02/2017	14

[swuehl]

Something like

=(Year([End Date])-Year([Start Date]))*12+(Month([End Date])-Month([Start Date]))

[maxgro · Accepted solution]

source:

load * inline [

Start Date, End Date

01/01/2016, 31/12/2016

01/01/2016, 31/07/2016

01/01/2016, 15/08/2016

01/01/2016, 28/02/2017

];

final:

load

  [Start Date],

  [End Date],

  (Year([End Date])*12 + Month([End Date]))

  -

  (Year([Start Date])*12 + Month([Start Date]))

  + 1 as NumberOfMonth

Resident

  source;

DROP Table source;

[#4]

Jeff try below:

(Year(EndDate)*12 + Month(EndDate)) - (Year(StartDate)*12 + Month(StartDate)) + 1

[Anonymous]

Hi Jeff,

in Script:

Months:

Load

  *,

  (End_Year - Start_Year)*12 + (End_Month - Start_Month) + 1 as NumberOfMonths;

Load

  Year(Start_Date) as Start_Year,

  Year(End_Date) as End_Year,

  Month(Start_Date) as Start_Month,

  Month(End_Date) as End_Month,

  Price

Inline

[

Start_Date,End_Date,Price

01/01/2016,31/12/2016,1000

01/01/2016,31/07/2016,5000

01/01/2016,15/08/2016,8000

01/01/2016,28/02/2017,14000

];

in UI:

=((End_Year - Start_Year)*12 + (End_Month - Start_Month) + 1)

Regards

Neetha