Calculate number of months occurring in current year only

[apthansh]

Hi,

I have 2 date columns

Start Date and End Date

ID   Start date    End date

1    1-Nov-16     31-Jul-17

2    1-Aug-17     31-Jul-18

Here I have to calculate number of months occurring in 2017 only.Output should be as below

ID   Start date    End date   NoOfMonths

1    1-Nov-16     31-Jul-17     7                          (b/w Nov-2016 and Jul 2017  total  7 months are in 2017)

2    1-Aug-17     31-Jul-18     5                          (b/w Aug-2017 and Jul 2018  total   5 months are in 2017)

How can I put this in script ?

Thank you very much

[sunny_talwar]

May be like this

Num(Month([End date])) as NoOfMonths

[adamdavi3s]

=((year([End date])*12)+month([End date])) - (((year([Start date])*12)+month([Start date])))

This basically creates a numeric 'period' for each date and takes one from the other

whoops didn't notice the 'only in 2017 bit'

=

(if([End date] > yearend(Today()), (((year(yearend(Today()))*12)+month(yearendt(Today())))),(((year([End date])*12)+month([End date]))))

-

(if([Start date] < yearstart(Today()), (((year(yearstart(Today()))*12)+month(yearstart(Today())))),(((year([Start date])*12)+month([Start date]))))

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  If([Start date] >= MakeDate(Year(Today())),

  If([End date] < MakeDate(Year(Today()) + 1), Month([End date]) - Month([Start date]), 12 - Month([Start date]) + 1),

  If([End date] < MakeDate(Year(Today()) + 1), Num(Month([End date])), 12)) as NoOfMonths;

LOAD * INLINE [

    ID,  Start date,    End date

    1,    1-Nov-16,    31-Jul-17

    2,    1-Aug-17,    31-Jul-18

];

[apthansh]

Thank you Sunny.Awesomeness...You are my hero

[vinieme12]

or just this

LOAD

*,

if(Year(Startdate)-Year(Enddate)=0,Month(Enddate)-Month(Startdate),if(Year(Startdate)=Year(Today()),12-Month(Startdate),Month(Enddate)-1)+1) as NoOfMonth;

LOAD ID,date(Date#(Startdate,'DD-MMM-YY'),'DD/MM/YY') as Startdate,date(Date#(Enddate,'DD-MMM-YY'),'DD/MM/YY') as Enddate Inline [

ID,Startdate,Enddate

1,1-Nov-16,31-Jul-17

2,1-Aug-17,31-Jul-18

];