Years before I started my blog I was creating online content in forums like Tek-Tips.com. I am still an occasional contributor there but years ago I was spending an hour or more every day answering questions and generating thousands of posts. It isn’t unusual for me to do a web search and find my own answers from long ago in the results.
Recently, I was trying to see if I had ever posted my formula for taking a date value and finding the last day of that month. I found one example in a TT post from 2001 and reading that thread made me smile. The thread is all about finding the last day of the month and the answers are pretty complex. They were even discussing the 100 and 400 year exceptions for leap year.
I was late to the party, but my formula only had 4 lines. This is the same formula in 2 lines:
Local DateVar Last := Date(DateAdd( 'm', 1 , {Orders.Order Date} ));
Last - Day(Last)
You put any date value (database field, parameter, CurrentDate function, etc) in place of the field {Orders.OrderDate} and the result is the last day in that month. The comments below show two alternate formulas submitted by some of my colleagues that take different approaches.
This tip was included in Vol. 4 of my Expert Techniques series. Each volume in the set costs $10 has 30 similar tips. The full list of topics is here.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)
Adam Butt of APB reports in Norway has sent me his version of the same calculation:
DateAdd(‘m’, 1,date(year({field}),month({field}),1))-1
If you want it to return a Date value instead of a DateTime value you can add the Date() function around it.
Ido Millet of Millet software sent in another version using the ability of DateSerial to cross months and years that I wrote about earlier:
DateSerial(Year(CurrentDate), Month(CurrentDate) + 1, 0)