How to make sure you are on the last day of the month

Friday 19 July 2019 @ 9:18 am

Lots of reports require that you compare two different date periods and I often calculate prior date ranges based on the current range. But you have to be careful when the end of your prior period falls in a month with more days the the current period month?

Here is an example. Lets say your current period is in June and your prior period ends 6 months earlier in December. Calculating the Start Date is simple. If your Current Start is 6/1/2019 you can use a simple DateAdd like this:

DateAdd ('m', -6, {@CurrentStartDate})

but if you try a similar formula for the End Date you won’t get the right date. The following formula will return 12/30/2018:

DateAdd ('m', -6, {@CurrentEndDate})

The same issue occurs when your prior period is a year before and your current period ends in February. If he prior year is a Leap Year your prior period end date will be off by one day, ending on the 28th rather than the 29th.

My solution involves adding one day before you do the DateAdd and then subtracting that same day back out again, like this:

DateAdd ('m', -6, {@CurrentEndDate} +1) -1

This works because adding the one day puts on you on the first of the month. This is always a clean calculation when moving forward or backward by months or years. Then once you get to the first of the month in the prior period you subtract one day which always puts you on the last day of the month prior.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






One Response to 'How to make sure you are on the last day of the month'

  1. Ken Hamady - August 24th, 2019 at 9:05 pm

    Luc Rascar, one of my colleagues in France, pointed out that you can do something similar by using day 0 with the DateSerial function, something like this:

    DateSerial(year(currentdate),month(currentdate)-5,0)

    Where [currentdate] is your starting date and [-5] is the number of months back you need to go. Day 0 of any month is one day before day 1.


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server