Last day of the month

[Anonymous]

Hi, I have this field

I want to put the last day of each Month, for example: 20171231

Can you help me? Thanks!

[clondono]

Try using MonthEnd() function

=Date(MonthEnd([USR_FCRMVH_MESCER]))

Regards,

Carlos

[clondono]

You can also add date formatting to return the date in any format you like:

=Date(MonthEnd([USR_FCRMVH_MESCER]))

Will return:          '12/31/2017'

=Date(MonthEnd([USR_FCRMVH_MESCER]), 'YYYYMMDD')

Will return:          '20171231'

[Anonymous]

Try this Sample:

=date(MonthEnd(makedate(mid(201707,1,4), mid(201703,5,2))),'YYYYMMDD')

or

=date(MonthEnd(makedate(left(201707,4), Right(201703,2))),'YYYYMMDD')

=date(MonthEnd(makedate(mid(USR_FCRMVH_MESCER,1,4), mid(USR_FCRMVH_MESCER,5,2))),'YYYYMMDD')

[jomar_ebonite]

Hi Juan,

Try this:

= USR_FCRMVH_MESCER & MonthEnd(Date#(USR_FCRMVH_MESCER,'YYYYMM'))

Regards,

Jomar

[PrashantSangle]

Date(MonthEnd(Date#(DateFieldName,'YYYYMM')),'YYYYMMDD')

Regards

[jonathandienst]

Watch out for MonthEnd() - it will add a time component to your date (23:59:59.999) which Date() hides but does not remove. This can cause all sorts of problems with associations and conditions. Rather use:

=Date(Floor(MonthEnd([USR_FCRMVH_MESCER])), 'YYYYMMDD')

or

=Date(MonthStart([USR_FCRMVH_MESCER], 1), -1), 'YYYYMMDD')

=Date(MonthStart([USR_FCRMVH_MESCER], 1) - 1, 'YYYYMMDD')

to ensure that you have a 'time-free' date value.

[sasiparupudi1]

Date (Floor (Monthend (Date#(yourdate,'YYYYMM'))),'YYYYMMDD')

[formosasol]

Jonathan you are missing a ( in the "Date(MonthStart([USR_FCRMVH_MESCER], 1), -1), 'YYYYMMDD')"

Should be "Date((Monthend(Data,1)-1),'DD/MM/YYYY') as LastDayofMonth"

[jonathandienst]

Actually an extra comma. Now fixed.