Date Math - comparing today to a certain date with a dynamic year

I’m pretty sure this isn’t possible, but I just want to make sure I’m not missing something. I have a column that contains only a year. More specifically, the year that a student graduates from high school. Does anybody know if it’s possible to do some date math where I can subtract today’s date from 05/31/year, where the year is dynamic based on another column? Basically, I’ll always have a date of May 31st, but the year will depend on the graduation year. I’m currently doing it in a google sheet, but I’d love to accomplish this in a glide math column. I’m trying to figure out how to dynamically build a date by joining the year to 05/31 and have it be recognized as a date so I can perform date math on it.

I’m sure I can dig up some sort of complicated math formula to do it, or some action that would write a template of the date to a date column, so I’m not looking for too much effort on anybody’s part. I’m just wondering if there is a simple way to use text based dates with date math.

Can’t you build a template column to join the 05/31 and the year, then build a Math column based on the template column? My assumption is that the math column would then allow the template to be seen as a date (with year).

Doesn’t look like it. It just converts it to a number.

For the configuration text, write DATE and see if it forces it to be a date.

2 Likes

Hey, that worked!, but maybe not in the way you think it worked. I think it exploits a bug that I remember from a while back and described below. When I typed in ‘X’ it defaulted to a random text column so it didn’t see the template as a date. When I typed in ‘Date’, it defaulted to a date column. That flipped the math column into a date style column. I was able to switch it back to ‘X’ and still keep it as a date column. So it does work…but it’s a little scary if it will keep working in the future, but I might go with it for now. I do think we still need a way to dynamically piece together or manually type in a date into a math column and have it recognized as a date for use in date math. Something like wrapping text with DATE() to have it converted within the math formula.

3 Likes

Glad it worked! I use this a lot to force the Math column to see certain things as dates — it would be great to know if this is intended behaviour, and if it is, should it be documented somewhere (like in hint text) to show people how to make Math into the desired format.

1 Like

Actually I think I’m still running into a bit of a bug like I mentioned in that other thread. The math column is formatting the template as a date, but glide still doesn’t seem to recognize it as a true date for use in math…so I might have to keep searching, or come up with some gross math to work it out. Maybe I can figure something out from what I learned in that System Time / GMT thread.

Is there a way to force it to be a date again by writing DATE at the start of that formula?

I also thought of maybe using the Yes Code column to do your Sheets formula inside Glide, then doing all of this.

No, that didn’t seem to help.

Yes Code might be the solution I’ll have to go with. Would definitely cut down on the extra columns.

I haven’t touched Glide for the past month or so. I’ll have to get acquainted with all of these new features. :wink:

Thanks for your help!

1 Like

try to based down to simple facts… date is nothing than a base multiply by days… so… years … should be too… today date- year … i did that few times with my dates calculations… and it works… but if i remember good… i had base on GS… you need a base for each year days account…
im reading over and over your question…and cant figure what is the goal? subtract today from 5/31?.. im lost

I need to compare today to May 31st of any year in the future. The goal is to get a numeric difference in years that I can use in an IF column to indicate which grade a student is in school. Assuming that the end of a school year is usually around May 31st, that’s when I want the value to flip, so on May 31st, a student could go from being in Grade 5 to Grade 6…or from being a Junior to a Senior.

I currently do it in the Google sheets using DATEDIFF and dynamically build the May 31st date and compare it today and it’s worked for over 2 years, but I want to move some of this logic into Glide and out of the sheet.

And to be clear, I only store the student’s graduation year. Not a full date.

I tried this with the Excel formula column.

Hopefully it gets you what you want.

Formula:

=FLOOR((DATE(A1,5,31)-TODAY())/365.25,1)

XC link to apply: https://glide-formula-column.dvdsgl.repl.co/

5 Likes

Awesome! That will do it.

2 Likes