Fun with Dates

I solved it—had to use a math column to convert the text to a date and then pass the math column instead:

Screen Shot 2021-02-10 at 10.48.35 AM

2 Likes

This is awesome. I can make completed task to be removed after a few months. Or I can do reminders for uncompleted task. Idea is on creating a task, a static date would be inputed. month from now, and in a second col. If today is past then show red color. Need to think. Anyway Will need to test this out very soon. Working on 2 projects currently. I am completely overloaded plus my normal day job.

1 Like

That’s weird. I had assumed that once the text is placed into a date column, it’s then converted to a date and becomes static. So you’re saying that the text version is still buried somewhere in that date column, which makes it dynamic? I’ll have to play around with that some more. I have some ideas that I’ve been thinking about to rework my calendar app to use methods like this to have reoccurring events, but with only one row.

Ya, if you take a look at the screenshots above in my second post, you can see that the “1 month from now” value resides in the spreadsheet and not the date. Converting it to a math column first, though, solves it.

3 Likes

That’s really good to know!

3 Likes

@Jeff_Hager What’s the best way to do “days since” in Glide? I feel like the time in a date time throws off any sort of floor/ceiling/rounding that I do. Should I strip out the time using a template column first? Hate to create unnecessary columns if it can be solved elegantly in a single Math Column.

Are you just looking for the fact that the day changed? So for example the difference from yesterday noon to today 9am is less than a day. Do you want a result of 1 day? Same if it was today 11:59pm, it would still be 1 day. And after midnight it would become 2 days?

Correct. If I create an event today, then date difference is 0 until 11:59 tonight. Tomorrow, that event will be 1 day since creation.

Ok, when I have a little bit of free time, I’ll see what I can come up with.

There is some stuff somewhere on here regarding ordinal dates, which might work for doing this. Unless I think of something simpler.

3 Likes

Guilty as charged :slightly_smiling_face:

@Robert_Petitto I think that ordinal date formula will work for your use case, yeah? Just convert all your dates to ordinals, then you can do a direct numerical comparison.

2 Likes

I promise you, once Americans get old enough and we are out of school we feel the same way as you do. I would rather use the metric system and celsius among other things. You don’t know how often I have to convert numbers to understand them.:frowning:

1 Like

That worked. LONG formula but end result is what I wanted:

(FLOOR(275 * MONTH(now)  / 9)
-
(FLOOR((MONTH(now) + 9) / 12)
*
(1 + FLOOR((YEAR(now) - 4 * FLOOR(YEAR(now) / 4) + 2) / 3)))
+
DAY(now)
-
30)-(FLOOR(275 * MONTH(date)  / 9)
-
(FLOOR((MONTH(date) + 9) / 12)
*
(1 + FLOOR((YEAR(date) - 4 * FLOOR(YEAR(date) / 4) + 2) / 3)))
+
DAY(date)
-
30)

2 Likes

There might still be a problem when crossing into a new year as the ordinal day would reset.

Didn’t really take time fully understand how that formula works, but I’ll still take a crack at it to see if I can find a better way. I’m sure there is. We just need to normalize the dates so they have the same time associated with both of them.

1 Like

Really struggling with this one. Tried converting Y/M/D to an epoch date, or some form of it, but for some reason, I couldn’t get it to calculate quite right. I thought I got really close, but then realized I didn’t have a valid count of days when comparing 12/31 to today. I expect 49 days, but couldn’t get it to calculate to correctly. Smaller date differences seemed to work, but something still wasn’t right.

I think this would be really easy with 2 columns One column to have a static date with a midnight time and a second column for the math to figure out the number of days. I just can’t figure out how to normalize the time portion of a date to midnight for both dates (Now and the entry date). I can easily do that with a seed date, then the date difference calculation is easy.

I’ll keep playing, but I have have to resort to a 2 column method.

2 Likes

This one really threw me for a loop. I’ll try to explain it the best that I can.

First the way that didn’t work. I think I understand why it doesn’t work but can’t really explain it. But, I couldn’t find a way to fix it. What I had tried to do was convert the date into a Unix Epoch date. There’s different ways to calculate it (preferably into seconds) but converting each date to the number of days from Jan. 1st 1970 was the goal. This is what I got. As you can see, it seems to work for the initial dates, but things got off once I started using older dates. As of today, I expected the number of days from Dec 31st 2020 to be 51 days.

After I thought about it for a good 2 days, I think I finally realized that I should be using the ordinal number of days for a year. I know I was having issues with the 31 days in January, but I couldn’t quite put my finger on what the problem was. So I decided to replace the Month and Day calculations with the ordinal days that we figured out before. This gave me the correct results, but I’m not 100% confident that it will work across multiple years. Plus it’s just ugly to look at.

Both formulas above only require a single column, but since I don’t feel great about them, I finally decided to throw in the towel and resort to 2 columns. This requires a seed date. Can be any date but I think the time needs to be midnight. What we are doing here is calculating the number of days different from the seed date to now, or to the date column. Then I’m using a MOD to pull out the decimal and then subtract that difference (hours/minutes) from the date. This normalizes each date to have midnight as the time for both dates. With an equal time, we can now calculate the number of days properly without having to worry about the time of day. It does require the seed column, which can probably be a single value column pulling a static seed date from some other sheet. If glide would allow us to set a replacement value of Today instead of Now, then this would have been super easy with a single column…or if there would be a way for us to manually specify a date in a math formula…or if I could have simply performed the MOD calculation directly on a date (it won’t let me).

I feel really good about this last method working consistently. You could even store the seed date in a user profile column and pull it into the replacements that way.

(NOW-MOD((NOW-SEED),1))
-
(DATE-MOD((DATE-SEED),1))

If anybody has any better ideas, I’m all ears.

4 Likes

The brain power and time you’ve spent on this is beyond appreciated. My only other solution would be to create a Template Column of the “date only” view of the date for both today column and for the date column. Then subtract the two. That requires three columns, but less brain power. Haven’t tested it but it’s assuming you can do date math on template columns? If not I would have to re-convert those template columns to math columns which should result in a 00 time. That would then require five columns.

1 Like

I would have to assume that you can’t do date math on template columns. At that point they become text strings and no longer a date. I think until we get more functionality, the seed date solution is probably the least amount of columns that will be required.

I’m sure I’m missing something obvious here, and I’m sure you’ll point it out, but…

Why can’t you:

  • Calculate the ordinal day for now, and add the year number to it
  • Calculate the ordinal day for the date to compare to, and add the year number to that
  • And then simply subtract the 2nd from the 1st?
(YEAR(Now) +
FLOOR(275 * MONTH(Now)  / 9)
-
(FLOOR((MONTH(Now) + 9) / 12)
*
(1 + FLOOR((YEAR(Now) - 4 * FLOOR(YEAR(Now) / 4) + 2) / 3)))
+
DAY(Now)
-
30)
-
(YEAR(Date) +
FLOOR(275 * MONTH(date)  / 9)
-
(FLOOR((MONTH(date) + 9) / 12)
*
(1 + FLOOR((YEAR(date) - 4 * FLOOR(YEAR(date) / 4) + 2) / 3)))
+
DAY(date)
-
30)

Seems to give the correct results?

Using the difference of Dec. 31st 2020 and Jan 1st 2021 as an example:

Because 2021001-2020365 does not equal 1 day (if you multiply year by 1000 first).

Same if you don’t multiply year by 1000 and simply add it to the ordinal day. You would get 2022-2385, which does not equal 1 day.

Once you start crossing years, then the ordinal day resets and throws off the numbers. Would be easy if there were 1000 days in a year, but we don’t have that…even though if feels like it sometimes. :wink:

3 Likes

Yeah… I tried this too. Awesome work.

2 Likes