Get a Date which is 1 Month prior to a particular Date

You can have use cases where you need to calculate a date 1 month prior to a particular date or even the current date. Or you may have to calculate a due date for product registration as one month after the date of purchase. In such cases, you need to use the EDATE functions which returns the serial number of the date that is the indicated number of months before or after the start date.

Syntax

EDATE(Start_date, no. of months)

So to find out a date 1 month prior to today

Formula

DATESTR(UNIXTIME(EDATE(TODAY(), -1)),“MM/DD/YYYY”)

where,
EDATE calculates the date 1 months prior to today’s date.


Now if it is for a particular date selected from a datepicker control and you want to find a date 1 month prior you would have to make a change as follows:

DATESTR( UNIXTIME( EDATE( DATESTR( datepicker5) , - 1)) , “MM/DD/YYYY”)

where
DATESTR converts the datepicker5’s selected date to a date format that can be read or used by EDATE function
EDATE calculates the date one month prior.
UNIXTIME converts the EDATE to UNIXTIME and is then displayed in the format “MM/DD/YYYY” as specified with the DATESTR function.