šŸ†• Date-time Math

The one for min/mins is basically identical.

1 Like

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.

5 Likes

Oh thanks so much Jeff!

1 Like

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.

1 Like

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))
4 Likes

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ā€

1 Like

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.

5 Likes

Thanks. That was extremely helpful. :exploding_head: :slight_smile:

Hi,

I need to do the subtraction like you showed above .
But my date column in IF ELSE column.
I made If Else Column IF ā€œStart Dateā€ ā€œis Beforeā€ ā€œFirst Jan 2021ā€.
I cannot be subtracted.
I try now - start date(Date Type Column) and its works.
Please help.

Hmmm, When I try this exactly like you do (I thinkā€¦) MATH sees it as a date format resultā€¦
I have to write TRUNC(now - date) to get it to see it as a numberā€¦

That behaviour has recently changed. By default subtracting one date from another now gives a number formatted as a duration.

As you noted, if you want a number (old behaviour) you need to use trunc or round.

4 Likes

One of the ways that I use DATEDIFF is to calculate whole years and days remaining after a year has passed. I prefer this way because the DATEDIFF function handles leap years for me. I know I could create the date calculation myself, but itā€™s so much nicer to use the function.

Iā€™m looking forward to being able to do something similar within Glide in the future, but for now, Iā€™m still tied to Google for this one.

Does anyone know of a straightforward way of doing this within Glide without recreating the wheel?

I agree the ā€œMDā€, ā€œYMā€ and ā€œYDā€ functions are great. You wonā€™t find a way that might be straightforward enough for your liking, but I think it will just take a few more math columns.

1 Like

It shows ā€œnot foundā€.
Could you please repost the link

HI! Could you please share the formula you use to calculate the time between now and a birthday, without requiring a person to enter their last birthday?

1 Like

I get an ā€œInvalid operation on date/timeā€ message on the part : year(now) - date