Get Date 6 months from now, display 1st day of the month

There would be some use cases like the validity of the warranty on a particular item being for 6 months from the date or till the starting date of that month. You may want to find the date of the expiration of the Certification issued for a product or so on.

Here you would need to find the date 6 months from the current date as well as provide it formatted as the first day of that month.

In this case, use the EDATE function which provides the date which is before or after the specified number of days.

Syntax

EDATE(start_date, no_ofmonths)

Formula

DATESTR(UNIXTIME(EDATE(TODAY(), 6)),“MM/01/YYYY”)
where

  • EDATE calculates the date 6 months from current date
  • UNIXTIME converts the output date to Unix time format
  • DATESTR displays the date in the specified format wherein the Day of the month is formatted to ‘01’.

If the current date is 08/05/2020 then the output would be 02/01/2021 i.e 6 months from August as Feb of next year.

In case you simply want the date 6 months from now then it would be as follows:

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