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]

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.

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… [image]

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.

[image] david: Date-time math (e.g. DATEDIFF ) is one of the most common Google Sheets formulas that people still rely on in their Glide apps, but as many of you know, relying on Google Sheets formulas can slow down your app, as Glide must wait for your Google Sheet to update, and then for Google to tell Glide about it. 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.

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?

🆕 Date-time Math

General

Dilon_Perera June 6, 2022, 3:09pm 127

Maybe this post by Robert will help here!

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

🆕 Date-time Math

Related topics