Create EndMonth Flag in Script

[salleninsd]

Hello,

I am trying to flag when a date is an End of Month date with an if statement.  It appears the syntax is correct, but my results are returning an 'N' on the last day of the month.  Here is the script:

if(Date(MonthEnd([Census_Daily.CensusDate],'YYYY-MM-DD')=[Census_Daily.CensusDate],'YYYY-MM-DD'),'Y','N') as vIsCensusMthEnd,

Any suggestions are most appreciated.  Thank you!

[petter · Accepted solution]

You don't need to do all the date fomatting stuff as long as all you need is a true or false to check against.

This should work for you:

If(Floor(MonthEnd([Census_Daily.CensusDate]))=Floor([Census_Daily.CensusDate]),'Y','N')

Be sure to use the Floor() function to remove any time part of the date. This is crucial since the MonthEnd() function doesn't only give you the last day of the month but also the very last minute and second before midnight on that date.

[salleninsd]

Hi Petter,

Thank you so much for the quick response!  I just tried, and am still getting an N for the flag:

[salleninsd]

Just saw the rest of your message with the Floor(), and will try that...

[salleninsd]

Wow!  Petter, you are my HERO!

Thank you so much for your help, it works!  I forgot about the timestamp possibility, and I've never used the Floor() function.  Learn something new every day!

Have a terrific evening!

[petter]

You're welcome - happy to help.