How to Add Dynamic Date Values

Follow

Comments

5 comments

  • 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?

    0
    Comment actions Permalink
  • Tom Cupit

    Does this mean that in order for me to simply add 7 days to today() I have to account for the variable number of days in each month using the NewMonth & NewYear columns Joel has provided the calculations for?

    0
    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • 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).

    0
    Comment actions Permalink
  • 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).

    0
    Comment actions Permalink

Please sign in to leave a comment.