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

[Jeff_Hager]

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.

@Jeff_Hager

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.