The Math column can now do math with date/times. In particular: Subtracting two date/times produces a duration, which is a number and is measured in days, like in Google Sheets. Adding a duration (number) to a date/time, or subtracting it from a date/time will add/subtract that many days to it. You can pick “Now” as a substitution for a variable. [image] [image]

[Screen Shot 2021-03-17 at 9.08.48 PM] The one for min/mins is basically identical.

Do you think it’s possible to use the NOW function in Glide to generate a random number when a form is submitted? I was thinking it would be nice to capture the time when a form is submitted then isolate the seconds … and then re-scale the value to fit between 0 - 1, e.g. multiply by (1/ 0.6) and divide by 100. Is there a maths function in Glide that could handle this task?! Thanks in advance if you can help :slight_smile:

You can isolate seconds out of Now using something like this in a math column. TRUNC(SECOND(date) /60*100) Or some variation of that depending on how large of a random number you want.

How do I add months and years and not days. I want to calculate when a warranty expires where warranty period is given in months or years. e.g Warranty Expiry = purchase date + 12months or 2 years Using 365 days for the year doesn’t always give you the correct day.

[image] Fun with Dates Project Showcase Here you go. Just use 1.5 years for the number of years, or you can take your number of months and divide by 12. Same thing either way.

Thanks Jeff, but that is what I am currently doing but it’s not 100% accurate. e.g I purchase a car battery on the 9 August 2021(purc_date), it has 18months warranty, so I expect it to expire on 8 Feb 2023. The formula I used was end_date = purch_date + (18/12*365) and the result is 7th Feb 2023. It’s one day off, probably to do with the 30/31 days per month Also, if I am understanding correctly I cannot do something like end_date =purch_date+ Month(18) as date calculations only work on “number of days”. Correct ?

So you are saying that you used one of the following formulas in a math column and the result was not accurate? The following formulas are from the post I shared above. If it’s not working, can you share a screenshot of the formula, the date being feed into it and the result so I can debug my formula? 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) Here is the formula to get the leap year date of Feb 29th to back date to Feb 28th instead of Mar 1st ((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))

My apologise, I didn’t fully appreciate the logic the first time around, but now I understand what you are doing and it works fine. This is FANTASTIC. Thanks Just one question what the purpose of the “+15”

Essentially what it’s doing taking the entered date, stripping out the DAY number, and subtracting that number from the date, so technically we end up with a date that is the last day of the prior month. Then we add 15 days to get a date that’s in the middle of the entered date’s month and year. So we end up with the 15th day of the month. Then generally, when you add a certain number of days converted from months or years, it will stay within that same month. The result may drift by a day or two off of the 15th of the month, due to months being different lengths, but by keeping it in approximately the middle of the month, the date will not drift out of that specific month. So that’s the first part of the formula. Then in the second part of the formula we do it again but this time get the Day number that we drifted to when adding months or years. Then we subtract that number from the date that we calculated in the first part of the formula, so now we end up with a date in the future that is the last day of the prior month of the goal date we are trying to calculate. Finally the last part of the formula adds back the day number of the entered date, so we end up with the correct day in the future. To illustrate your example date of 9 August 2021: ((Now-DAY(Now)+15)+(Years*365.25)) Takes your date and subtracts the day number so we end up with: 31 July 2021'. Then adds 15 days to get 15 August 2021’. Then add 18 months which converts it to `14 February 2023’ DAY((Now-DAY(Now)+15)+(Years*365.25)) This part does the same calculation but only return the day number of 14 from that future date. We subtract 14 days from 14 February 2023 to get 31 January 2023 DAY(Now) Finally we determine the day number from the entered date which is 15 and add it to the 31 January 2023 date to get 15 February 2023. The catch is when dealing with February 29th of a leap year. The question is how would you expect it to handle that future date if the future date is not a leap year. My first formula will end up converting Feb 29th to Mar 1st of a non-leap year. The second formula does more work to determine if the month is different (will always be 0 or 1), and subtract that difference as days. So if Feb 29th ended up being Mar 1st in a different year, then it would see the difference in months and subtract one day to get back to Feb 28th of that non-leap year.

🆕 Date-time Math

General

Jeff_Hager August 31, 2021, 3:50pm 108

Topic		Replies	Views
Fun with Dates Project Showcase	156	7731	April 19, 2022
New computed column: Math General	95	6895	November 12, 2021
📅 Guide on Dates & Times Community Resources	22	1888	April 2, 2025
🗓 Month, Day, and Year Math Functions Ask for Help	47	4109	July 15, 2023
If then else with DATE Ask for Help	28	3521	May 25, 2022

🆕 Date-time Math

Related topics