# Date Math - comparing today to a certain date with a dynamic year
**URL:** https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451
**Category:** Ask for Help
**Created:** [August 13, 2021, 10:38pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451 "2021-08-13T22:38:10Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 10:38pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/1 "2021-08-13T22:38:10Z")
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.
---
### Author:  [@kyleheney](https://community.glideapps.com/u/kyleheney)
#### Post date: [August 13, 2021, 10:41pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/2 "2021-08-13T22:41:36Z")
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).
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 10:44pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/3 "2021-08-13T22:44:24Z")
Doesn’t look like it. It just converts it to a number.
---
### Author:  [@kyleheney](https://community.glideapps.com/u/kyleheney)
#### Post date: [August 13, 2021, 10:45pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/4 "2021-08-13T22:45:14Z")
For the configuration text, write DATE and see if it forces it to be a date.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 10:55pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/5 "2021-08-13T22:55:24Z")
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.
> [@clock3 System/GMT Time - Solved!](https://community.glideapps.com/t/system-gmt-time-solved/27524/1):
>
> One bug I ran into is that creating the date with GMT template will allow it to convert to a date, and I can pick out pieces of it, such as year/month/day/hour/minute/second, but using it for basic date math just would not work (even when Math converting it to a date, it was still weird). I think behind the scenes it’s seen as a date, but not quite. Kind of hard to explain, but I really fought with the math while trying to account for the hour of the day crossing different days. I was trying to do a simple date subtraction to get the number of days different, but it just wouldn’t do it. Also the formatting in the math column would only follow the column type from the previous selection. So, in the dropdown, if I picked a number column for the math replacement value, then my GMT date column, it would only give me the Precision rounding option. If I first picked a date column, then my GMT date column, then I would get the date formatting options. Really weird, and I feel like I’m exploiting a bug a little bit to make this work…kinda…but not really. It’s almost like my GMT date column doesn’t really have a type, although it sort of acts like a date column. Anyway the offset math was a little more gross than it should have been, but still not too bad. I still figured out an alternative that seems to work well.
---
### Author:  [@kyleheney](https://community.glideapps.com/u/kyleheney)
#### Post date: [August 13, 2021, 10:58pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/6 "2021-08-13T22:58:56Z")
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.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 11:11pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/7 "2021-08-13T23:11:44Z")
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.
---
### Author:  [@kyleheney](https://community.glideapps.com/u/kyleheney)
#### Post date: [August 13, 2021, 11:15pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/8 "2021-08-13T23:15:00Z")
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.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 11:25pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/9 "2021-08-13T23:25:23Z")
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. 😉
Thanks for your help!
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [August 13, 2021, 11:31pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/10 "2021-08-13T23:31:03Z")
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
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 13, 2021, 11:54pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/11 "2021-08-13T23:54:14Z")
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.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [August 14, 2021, 12:29am UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/12 "2021-08-14T00:29:15Z")
I tried this with the Excel formula column.
Hopefully it gets you what you want.
Formula:
```auto
=FLOOR((DATE(A1,5,31)-TODAY())/365.25,1)
```
XC link to apply: [https://glide-formula-column.dvdsgl.repl.co/](https://glide-formula-column.dvdsgl.repl.co/)
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [August 14, 2021, 12:37am UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/13 "2021-08-14T00:37:56Z")
Awesome! That will do it.
---
### Author:  [@NoCodeAndy](https://community.glideapps.com/u/NoCodeAndy)
#### Post date: [January 17, 2024, 4:50pm UTC](https://community.glideapps.com/t/date-math-comparing-today-to-a-certain-date-with-a-dynamic-year/30451/14 "2024-01-17T16:50:53Z")