How to Add Dynamic Date Values

Goal

Add dynamic date values in a worksheet.

The function ADDTODATE might not fit in every use case, as the second argument need to be a constant value. If the add on value is not static, a different approach is necessary.

Date (DATE), AddOn (STRING)
2015-02-26, 90
2015-02-26, 120
2015-02-26, 180 

Learn

Lets assume that the given STRING is from value DAY, so first calculate how long is a day in milliseconds.

#AddAsMs
=INT(#AddOn)*24*60*60*1000
Date (DATE), AddOn (STRING), AddAsMs (INTEGER)
2015-02-26, 90, 7776000000,
2015-02-26, 120, 10368000000
2015-02-26, 180, 15552000000

Than convert the DATE into milliseconds (INTEGER) via TIMESTAMP

#DateAsMs
=TIMESTAMP(#Date)
Date (DATE), AddOn (STRING), AddAsMs (INTEGER), DateAsMs (INTEGER)
2015-02-26, 90, 7776000000, 141678360000
2015-02-26, 120, 10368000000, 141678360000
2015-02-26, 180, 15552000000, 141678360000

add them up and convert them back via MSTODATE

=MSTODATE(#DateAsMs+#AddAsMs)

Comments

  • Avatar
    Slaven Sljivar

    This works for dates, which have a fixed number of milliseconds. But, how would one add months, which have a variable number of milliseconds?

  • Avatar
    Joel Stewart

    If adding a static value to all rows, the ADDTODATE function can accommodate this natively.

    This article describes the technique to add different values of dates to different rows. In this circumstance, it is important to ensure that the AddOn column contains the pre-calculated durations to be added (i.e. 28, 29, 30 or 31 days for each month).

  • Avatar
    Slaven Sljivar

    Thanks for the super-quick response. Yes, in my case, I need to add dynamic values.
    One column (col1) contains the date field, and the other (col2) contains the number of months to be added to the first column. In SQL, I would do this with dateadd('month', col2, col1).

    Edited by Slaven Sljivar
  • Avatar
    Joel Stewart

    In that case, I would use another approach that utilizes FORMATDATE and ASDATE functions together. For purposes of explanation, I'll separate out the new components into more detail (these could be nested though):
    Create a column called NewMonth: INT(FORMATDATE(col1;'MM'))+col2)%12 - here the %12 ensures that we rotate from December to January when needed.
    Create a column called NewYear: INT(FORMATDATE(col1;'YYYY'))+(INT(FORMATDATE(col1;'MM'))+col2)/12) - here we are adding 1 year if we went from December to January when needed.
    Lastly, you'll build the new date:
    ASDATE(T(NewYear)+T(NewMonth)+FORMATDATE(col1;'dd');'yyyyMMdd')

    There may be some peculiar formatting cases to pay attention to with '01' being converted to '1' as an INT and back to '1' as a STRING. You can use this article to create leading zeros if needed: https://datameer.zendesk.com/hc/en-us/articles/204067184-How-to-Export-with-Leading-Zeroes-Into-a-CSV-File

    Lastly, there are some circumstances here that require some further thought. For example, if the date is December 30, 2015 and 2 months are to be added, the formula would try to output February 30, 2016 (an impossible date). Think carefully about how these are intended to be handled in your particular use-case.

    One last note, you'll