Getting the LATEST day of a month, but not necessarily the LAST day

[Eric.Seitz]

Howdy folks!

I'm having a bit of trouble with one report, I'm not even sure if this is doable but here it goes.

I have a date field that contains dates when data was entered into the system. I'm trying to find the LATEST date per month but that doesn't necessarily line up with the LAST day of the month.

For example the data may look something like this:

Enter Date
------------
June 1st
June 5th
June 29th
June 30th
October 5th
October 16th
October 23rd
December 5th
December 31st

And I'll want it to end up like this:

Enter Date Filtered
------------
June 30th
October 23rd
December 31st

Basically, per month I need to filter out any date that isn't the "latest" date. Any ideas? I've been playing around with 'MAXIMUM' but I can't get it to work quite right.

Thanks!

[MFGF]

Howdy folks!

I'm having a bit of trouble with one report, I'm not even sure if this is doable but here it goes.

I have a date field that contains dates when data was entered into the system. I'm trying to find the LATEST date per month but that doesn't necessarily line up with the LAST day of the month.

For example the data may look something like this:

Enter Date
------------
June 1st
June 5th
June 29th
June 30th
October 5th
October 16th
October 23rd
December 5th
December 31st

And I'll want it to end up like this:

Enter Date Filtered
------------
June 30th
October 23rd
December 31st

Basically, per month I need to filter out any date that isn't the "latest" date. Any ideas? I've been playing around with 'MAXIMUM' but I can't get it to work quite right.

Thanks!

Hi,

Are these real dates, or character strings? If they are real dates, then yes - maximum([Enter Date] for [month item]) would be the way to go.

If they are character strings, things get more tricky. You'd either need to convert them to real dates, or extract the day part from each string, convert it to a number, and get the maximum of this for each month.

Cheers!

MF.