I am trying to add Math Column for date calculation but as soon as select the columns, the screen goes blank. This happened with me four times. Am I doing something wrong?
Most likely a bug, but the bug is probably being caused because once you select a date, it’s immediately trying to add a Date to Row ID, which isn’t possible. The symbols in Row ID may be causing issues with the math to. Do you have issues if you first change the replacement for ‘D’ to something other than Row ID? Then change the replacement for ‘S’ afterwards.
2 Likes
Thanks @Jeff_Hager I will try that.
1 Like
Jeff, you were correct. When I change Row ID, it works well. Thank you for this.
2 Likes
@Jeff_Hager Jeff, I need your help further for calculation.
I have Start Date, and I have number of years, I need to add Number of Years to the Start Date.
For Example, if Start Date is 10th December, 2020 and Number of year is 2 Years, then End Date should exactly be 10th December, 2022. So the date remains same but the year changes.
Any idea how can I achieve this?
I tried to add days, but it does not achieve for all rows because of leap year.
In your math column, my first thought would be to try taking the date plus number of years multiplied by 365.25. So something like this S+(D*365.25). But that probably wouldn’t be entirely accurate. It would probably work if the start date was a leap year and the time was always midnight, but not for other years.
Since Glide only handles date math in days, we would probably need them to add functionality to add months or years as well. Google sheets has an EDATE function which let’s you add a duration in months, so to add a year, you would add 12 months. Would you be opposed to a google sheet formula, or would that cause too much lag in your app? I’m sure we could figure it out with a more complicated math formula and possibly a seed date of a know leap year, but I’d have to take some time to experiment with that.
3 Likes
Thank you Jeff for detailed explanation.
I guess I will have to shift to Google Sheet formula for that. I will see if that has some lag or not.
I will revert to you
1 Like
Hi @Jeff_Hager Hope you are doing well.
Did you manage to get any math formula which can be used in Glide? As of now, as you suggested, I am using EDATE in Google Sheet, which is creating lag of 2-3 seconds (which is fine as of now).
I think I have some new ideas. What’s the max number of years you would most likely be adding? I think I can make something work if it’s not more that 50 or 75 years.
1 Like
Thank you for response.
I am only looking for 1 Year, 2 Years and 3 Years. Not more than that.
This is the my Form wherein I am using it.
I want Expiry date should be automatically calculated based on Start date and Validity.
OK, I need to get back to work at my paying job, so I’ll come back to this a little later.
Meanwhile, for inspiration:
One more question, what would you expect for a result if the date was a leap year Feb 29th?
Thank you.
If the Start date is 29th Feb, then the end date would be the last date of Feb of that particular year (i.e. 28th Feb) considering adding 2 year or 3 years.
I would expect March 31st, and your function is fine here too.
1 Like
Edit: I have some updates to this formula that I’ve explained in the post below.
You’re requirement to back Feb 29th by a day if it’s not a leap year does complicate it a bit, but that’s not a problem. It just makes for a formula that’s much larger.
Here is the formula if we were to allow Feb 29th to roll over to Mar 1st.
((Now-DAY(Now)+15)+(Years*365.25))
-
DAY((Now-DAY(Now)+15)+(Years*365.25))
+
DAY(Now)
To get the leap year date of Feb 29th to back date to Feb 28th instead of Mar 1st, then we need to duplicate a lot of the formula and calculate the month offset to subtract the number of months different as days. So the difference of the current month of February (2), is subtracted from the calculated date of March (3), and the difference (1) is subtracted as a day which rolls March 1st to February 28th.
((Now-DAY(Now)+15)+(Years*365.25))
-
DAY((Now-DAY(Now)+15)+(Years*365.25))
+
DAY(Now)
-
(MONTH(((Now-DAY(Now)+15)+(Years*365.25))
-
DAY((Now-DAY(Now)+15)+(Years*365.25))
+
DAY(Now))-MONTH(NOW))
I feel pretty comfortable that this will work with virtually an unlimited number of years since we are still accounting for leap years by multiplying by 365.25 instead of just 365.
5 Likes
The 15 magic number!
2 Likes
It’s the save your butt number! 
I thought it was going to bite me here and I would have maybe needed to change it to 28 or something like that. At first I used 365 instead of 365.25, so every 4 years the days would drift and the formula only worked to add up to 55 or 56 years before the days no longer lined up. Originally planned to make it 28, so we could add up to 75 or 100 years, but with the .25 it seems to allow for adding hundreds of years without any issues.
2 Likes
Thank you @Jeff_Hager for this. I will check and revert.
1 Like
Hi @Jeff_Hager, hope you are well.
Since we have plug-in to use (Thanks to Glide Team), I want to use EDATE formula from Google Sheet to Glide Table.
I am trying to set it up here but not working. Am I doing it wrong?
or
I’m not sure why it doesn’t work. I tried a few different things but didn’t have any luck. I’m guessing it’s not liking something with the way that the date is being formatted. The only way I could think of to make it work is to parse apart the date into separate year, month, and day columns, then put it back together in a template column to pass into the formula, but I’m not sure that will even work. The only way I could get the EDATE formula to return anything was to manually enter a date instead of an A1 parameter. Even then it gave me the date in serial number format, which isn’t what you would want, so you would then have to add more to the formula to get a valid date.
This is the only thing that remotely worked. Even then it seemed to recognize my date in the DD/MM/YY format instead of the MM/DD/YY format that I’m used to:
=TEXT(EDATE(DATEVALUE("08/11/21"),A2),"DD/MM/YYYY")
This returned 08/04/2022 when I passed in 5 as the number of months into A2.
If it were me, I’d probably stick with one of the math formulas I provided above, since I know they work pretty well and use native glide functions.