Date math lacking precision

[David_Gabler]

I have a start day, current date (last swiped) and an end date.

I am trying to get computed columns that will tell me what day I am on, and how many days left.

“End” is a math/date column defined as start date + days

Computing End - Last Swiped (this day) seems to give me the hours left (which I could use later, but not now) and no way to format it into just the “day” part if I divide by 24.

And where is the “precision” that is normally found in math columns?

Like I said, at some point, getting to the hours left will be sweet, but right now I just need days (as a whole number)

[ThinhDinh]

Have you tried wrapping it within a ROUND function?

This is a recent addition from Glide, where math columns involving dates would result in durations instead of numbers, but I would love the option to choose between “duration” and “number” in this case.

[David_Gabler]

@ThinhDinh

I would love the option to choose between “duration” and “number” in this case.

Me too. Your solution worked, and I didn’t have to divide by 24. But does the Round function allow me to still get the number of hours remaining, which I can use in a different case scenario? The “duration” looks like it is HH:MM:SS. I assume they implemented it for their EVENT picker.

It does kind of violate one of the cardinal rules of functions, however. When the date math was introduced for dates, they specified that it would work for days. Changing that two years later, to introduce the EVENT picker, they should have introduced a new function with it like DURATION(date + date) in order not to break existing apps.

This fixes me for now, but next week when I’m implementing the same thing by hours instead of days, I’ll probably be back asking more questions…

[Darren_Murphy]

If you need hours, just do the same thing and multiply by 24…

For minutes, multiply by 1440…

And so on…

I’m not sure this change is related to the event picker. I think instead it’s somehow related to the new duration column and fact that was required for Airtable support.

The calculation is still returning a number (of days), it’s just that it’s now formatted as a duration for display. You can see this if you click into one of the values in the data editor:

[David_Gabler]

Thanks. Then I just found that there is a discrepancy:

You can see I have two entries with a start date of 2/20/2022 with a duration of 8 days and a last swiped date of 2/20/2022. One shows 7 days left and the other shows 8 days left. They both end on 2/28/2022. One shows the current day in the event is 1, the other shows the current day is 2.

They were both entered at different times of day, but my dates are “short” “date only”. Is there a way to force the calculation to only look at the DATE portion? Or to have each date start with a 00:00:00 time without forcing the user to have to see the time involved as they pick the start date?

This is starting to get more complex than I expected.

[Darren_Murphy]

In this case, instead of using round(), use trunc()

[Jeff_Hager]

Are you using a date picker or a date/time picker to select the date?

[David_Gabler]

Thanks, Jeff – you fixed me on that score last week, though it was a different app!
The only date I am letting them key in is the start date:

But based on the data editor, it’s storing the date like this:

And the calculated end date looks like this:

[David_Gabler]

@Darren_Murphy

trunc()

That didn’t seem to do it. Now three say 7 days left, but one ends on a different date – it did the same thing with ROUND(), though.

Here they are with neither trunc nor round. You can see the hours varies as though the calculation is being done from the time of entry rather than 00:00:00

[Darren_Murphy]

Actually, yeah - that makes sense because the trunc() is operating on the result of the calculation, so it’s a bit like shutting the gate after the horse has bolted.

hehe, okay… so what you could do is use a math column to first convert each of the dates into midnight on the date in question. You could use something like this:

Start
-(Hour(Start)/24)
-(Minute(Start)/1440)
-(Second(Start)/86400)

And then use those two columns to calculate the duration in days (using round or trunc - it shouldn’t matter which now). So that will give you something like this:

Edit: I just noticed from your previous reply to Jeff that your End Date is calculated. So you’d only need to apply the above to your start date, and then calculate your end date using the result. hmm, but if your end date is calculated, why do you then need to calculate a duration - wouldn’t you already know that? haha, clearly I’m missing something

[Robert_Petitto]

@Darren_Murphy

use a math column to first convert each of the dates into midnight on the date in question

I use this trick often. It should solve the issue. Nice work, @Darren_Murphy

[David_Gabler]

@Darren_Murphy

but if your end date is calculated, why do you then need to calculate a duration - wouldn’t you already know that? haha, clearly I’m missing something

Thanks, I’ll give this a try.

I’m calculating the “days left”, not the entire duration. I’m also calculating which day we are on in the duration.

Update, curious why the first row has 45 minutes added onto it? Have I achieved the long-hoped-for “day longer than 24 hours”?

[David_Gabler]

Hey Jeff,

I think the culprit may actually be the “last swiped” date, which is set in a custom action, and there we have no choice but to include the time:

[David_Gabler]

Hey Darren,

Last swiped was actually the culprit in this whole scenario.

Last Swiped - (Hour(Last Swiped)/24)- (Minute(last Swiped)/1440) - (Second(Last Swiped)/86400)

This worked like a charm.

Thanks!

[Darren_Murphy]

ah, okay.

You could actually nip this in the bud by creating a similar math column, but applying the formula to “Now”. And then use that to set the last swipe time. But the end result would be the same.

Glad you got it sorted. Date math with Glide is a bunch of fun, just ask @Jeff_Hager

[David_Gabler]

@Darren_Murphy

creating a similar math column, but applying the formula to “Now”. And then use that to set the last swipe time.

That’s actually a great idea, and if I have that column on my USER table, it will be available to all my other tables whenever I need it.