Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm curious if there's a way I can extract months between two dates... I can get the number of months between 2 dates, but I want to be able to get the actual month names... For example;
Start.Date = 01/01/2017
End.Date = 31/03/2017
Months between two dates = 3.
Now, I want to output those months in for format of the actual month names,so output should be... Jan, Feb, March...
How can I achieve this?
Cheers
you re welcome
here it is a modified version of the previous script, containing the generalization in case of multiple rows and the connection among the remaining months and month names
months:
LOAD start_date,
end_date,
month(end_date)-month(start_date) as NofMonths,
month(start_date) as month_start,
AutoNumber(start_date&end_date) as key,
RowNo() as rows
FROM
months.xlsx
(ooxml, embedded labels, table is Sheet1);
nrows:
LOAD max(rows) as nrows Resident months;
let nrows=Peek('nrows',0,'nrows');
DROP Table nrows;
for nrow = 0 to $(nrows)-1
let MonthStart=num(Peek('month_start',$(nrow),'months'),00);
let vKey=Peek('key',$(nrow),'months');
let number=Peek('NofMonths',$(nrow),'months');
month_names:
LOAD Month(date#(01&num#($(MonthStart),00)&2000,'DDMMYYYY')) as month_name,
$(vKey) as key AutoGenerate 1;
for i=1 to $(number)
let vCurMonth=num($(MonthStart)+$(i),00);
Concatenate (month_names)
LOAD Month(date#(01&num#($(vCurMonth),00)&2000,'DDMMYYYY')) as month_name,
$(vKey) as key AutoGenerate 1;
NEXT i;
next nrow;
It only works for the year 2000. what happens when the months are of different year