Get a Date which is 5 days prior to Today

Now let us consider that we want to calculate the date which is a few days e.g 5 days prior to today’s date.

Formula:

With Time

STRTOUNIX(TODAY()) - N * 24 * 60 * 60 * 1000

Where

  • TODAY() function will return Date object with current date & time values.
  • STRTOUNIX(TODAY()) will convert given date object to corresponding UnixTime value which is number type
  • Now, we will subtract number of days in milliseconds using N * 24 * 60 * 60 * 1000 formula. Value of 1 day in milliseconds is 24 * 60 * 60 * 1000.

Independant of Time

STRTOUNIX(DATESTR(TODAY(),“DD-MM-YYYY”),“DD-MM-YYYY”)- N * 24 * 60 * 60 * 1000

  • TODAY() function will return Date object with current date & time values.
  • DATESTR(TODAY(),“DD-MM-YYYY”) will return the given date as String Text as DD-MM-YYYY format
  • STRTOUNIX(DATESTR(TODAY(),“DD-MM-YYYY”),“DD-MM-YYYY”) will convert given date String to corresponding UnixTime value which is number type
  • Now, we will subtract number of days in milliseconds using N * 24 * 60 * 60 * 1000 formula. Value of 1 day in milliseconds is 24 * 60 * 60 * 1000.

You can also refer to this link here for a detailed explanation.