Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have this field
I want to put the last day of each Month, for example: 20171231
Can you help me? Thanks!
Try using MonthEnd() function
=Date(MonthEnd([USR_FCRMVH_MESCER]))
Regards,
Carlos
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'
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')
Hi Juan,
Try this:
= USR_FCRMVH_MESCER & MonthEnd(Date#(USR_FCRMVH_MESCER,'YYYYMM'))
Regards,
Jomar
Date(MonthEnd(Date#(DateFieldName,'YYYYMM')),'YYYYMMDD')
Regards
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.
Date (Floor (Monthend (Date#(yourdate,'YYYYMM'))),'YYYYMMDD')
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"
Actually an extra comma. Now fixed.