16 Replies Latest reply: Aug 30, 2017 6:11 AM by Anand Chouhan

# last day and not last day

Hi Folks,

i got a situation where i have my Datafield for instance:

14.06.2017

16.06.2017

30.06.2017

how can i determine the last day in each month, i want to achieve this results:

14.06.2017 - not last day

16.06.2017 - not last day

30.06.2017 - last day

Does have anybody any idea?

Thanks a lot

Beck

• ###### Re: last day and not last day

am not sure,

but you can create that date column as  lastday  ins cript

like

date(datecolum) as Lastday

• ###### Re: last day and not last day

Chanty

thanks a lot for your feedback

Beck

• ###### Re: last day and not last day

Hi,

maybe

IF(Date(DateField)=Floor(MonthEnd(DateField)),'Last','No Last')

Regards,

Antonio

• ###### Re: last day and not last day

Antonio thanks a lot for your quick response and help

Beck

• ###### Re: last day and not last day

For this better you can create one single Flag field in script part and then use it here on front end,see the script for that.

Ex:-

LET vMinDate = Num(YearStart( Today() ));
LET vMaxDate = Num(YearEnd( Today() ));

Calendar:
Date( \$(vMinDate) + IterNo() - 1 )          as [Date Field],
Month(Date( \$(vMinDate) + IterNo() - 1 ))   as [Month Field],
MonthEnd(Date( \$(vMinDate) + IterNo() - 1 ) ) as [Month End],
Year(Date( \$(vMinDate) + IterNo() - 1 ))    as [Year Field]
AutoGenerate 1
While (\$(vMinDate) + IterNo() - 1) <= \$(vMaxDate);

NoConcatenate
FinalCal:
*,
If(Floor([Date Field]) = Floor([Month End]),'Last Day','Not Last Day') as DateFlag
Resident Calendar;
DROP Table Calendar;

• ###### Re: last day and not last day

Hi Anand,

thanks a lot for your feedback,

my issue is: if i find the last day of month, then i want to add one month, for instance:

if i find: 31.03.2017 then = 30.04.2017

but in the case of feb i have a deviation: 28.02.2017 = 28.03.2017 the right result would be: 31.03.2017

Do you have an idea?

Beck

• ###### Re: last day and not last day

Ok then try this first find Monthend and then Addmonths to one month MonthEnd( AddMonths([Date Field],1) ) and rest are ok.

If(Floor([Date Field]) = Floor([Month End]), MonthEnd( AddMonths([Date Field],1) ),[Date Field]) as NewDateField

• ###### Re: last day and not last day

Thanks a lot Anand for your help

Beck

• ###### Re: last day and not last day

Welcome Beck

• ###### Re: last day and not last day

Is this you required.

• ###### Re: last day and not last day

Antonio,

i have a question, if i find the last day in month, i want to add one month, for instance

30.06.2017 + 1  = 30.07.2017

i created this expression, but it does not work

if(Date(DATUM) = Floor(MonthEnd(DATUM)),AddMonths(DATUM,1),'NOT') as test

am i missing somthing?

Thanks a lot

Beck

• ###### Re: last day and not last day

Antonio thanks a  lot for your feedback,

in my issue: i got a case : if i have a month: feb: 28.02.2017 my function gives me: 28.03.2017 the correct result would be = 31.03.2017.

did i something wrong?

Thanks a lot

Beck

• ###### Re: last day and not last day

Replace AddMonths with MonthEnd

Date(MonthEnd(Datum,1))

• ###### Re: last day and not last day

Thanks a lot Antonio for your help

Beck

• ###### Re: last day and not last day

You can try this way also use Floor on both the sides

If(Floor([Date Field]) = Floor(MonthEnd([Date Field])),'Last Day','Not Last Day') as DateFlag