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 23 seconds (which is fine as of now).
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 23 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.
Thank you for response.
I am only looking for 1 Year, 2 Years and 3 Years. Not more than that.
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.
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.
((NowDAY(Now)+15)+(Years*365.25))

DAY((NowDAY(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.
((NowDAY(Now)+15)+(Years*365.25))

DAY((NowDAY(Now)+15)+(Years*365.25))
+
DAY(Now)

(MONTH(((NowDAY(Now)+15)+(Years*365.25))

DAY((NowDAY(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.
The 15 magic number!
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.
Hi @Jeff_Hager, hope you are well.
Since we have plugin 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.
Thanks @Jeff for your time and explanation. I tried to check changing the format of the date in Google Sheet, but it did not work. I guess I will still stick to Google Sheet only, unless this starts working correctly.
Or you can just use one of the math formulas I gave you back in January, in a math column. There is no lag using those. Whatever works best for you.
Yes, I tried to understand that formula but I felt I was incapable of understanding it and frankly speaking, I try to avoid using something which I don’t understand (unless that’s the only way of doing it).
Having said that, I have bookmarked this formula for knowledge purpose and I am sure, one day I will be able to understand how to create such formula just like you. You are an inspiration! Thank you
Closing due to inactivity. This topic will be deleted in a few weeks if there are no more comments.