Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Something like
=(Year([End Date])-Year([Start Date]))*12+(Month([End Date])-Month([Start Date]))
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;
Jeff try below:
(Year(EndDate)*12 + Month(EndDate)) - (Year(StartDate)*12 + Month(StartDate)) + 1
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