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, 902015-02-26, 1202015-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, 103680000002015-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, 1416783600002015-02-26, 120, 10368000000, 1416783600002015-02-26, 180, 15552000000, 141678360000 |
add them up and convert them back via MSTODATE
=MSTODATE(#DateAsMs+#AddAsMs) |
Comments
5 comments
This works for dates, which have a fixed number of milliseconds. But, how would one add months, which have a variable number of milliseconds?
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).
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).
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
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?
Please sign in to leave a comment.