Announcements
cancel
Showing results for
Did you mean:
Contributor III

Calculate number of months between dates in script

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.

 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
1 Solution

Accepted Solutions
MVP

source:

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:

[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 Replies
MVP

Something like

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

MVP

source:

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:

[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;

Not applicable

Jeff try below:

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

Anonymous
Not applicable

Hi Jeff,

in Script:

Months:

*,

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

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

Community Browser