Okay, so what I’d do is use an indexed 14 row helper table.
Start by using a math column to calculate the current date, and then use a second math column to take that and add the index.
I’d actually suggest a different approach to the one that @nathanaelb offered. Whist that approach is fine for some use cases, the problem is that it doesn’t subtract the milliseconds component from the datetime, and so you wind up with a datetime that is a few milliseconds after midnight. And this can cause off by one errors that can be quite difficult to debug.
What I’d suggest instead is to convert the current datetime to an integer in YYYYMMDD format, and then work from there. So using the below formula:
Year(Now) * 10000
+ Month(Now) * 100
@Darren_Murphy @nathanaelb thanks for input. I think I will go with Darren’s suggestion. Seems to work as I want:+1:
@Darren_Murphy i wonder if you have used this approach when the future 14 days includes a change of month which will happen.
I might also take the past 14 days in the final setup. Same problem with month (and year) changing
Yeah, I think that gets a little hairy if you cross months and you’re working with numbers instead of dates. @Darren_Murphy has mentioned a few times that milliseconds can get in the way, so that’s why he converts them to numbers. I have a roundabout solution that should work and give you dates that are still actual dates, but the time is definitely midnight without worrying about milliseconds getting in the way.
The only part that’s not ideal is that you need a seed date. It can be any date, but the time has to be set to midnight. Then you can use it with some math to figure out the decimal portion of Now (hours/minutes/seconds/milliseconds) and subtract that from Now to get today’s date at midnight. It’s not bad, but it requires that extra seed date column, which is annoying.
Once you have today’s date at midnight, then you can populate it across all rows and add the index number as Darren showed. All resulting dates will still be true dates.
@Jeff_Hager Thanks for looking into my problem.
I think I now might have found a solution which is quite simple
I have a Now column which is just the time of today. Then a Date column which is a math column with Date+Index
Finally Date_text which is template column of Date column. The template column removes the hours, minutes, seconds
Maybe I’m just lucky - but it has worked in another context - and for a long time
As long as it works for you, that’s all that matters.
Just two things to keep in mind:
Your Date column still contains an underlying time, so if you eventually need it for some date comparisons, just be mindful that the time is still in there and could bite you, even though you have it formatted to only show the date without time. That’s why we suggested ways to get the hours/minutes/seconds/milliseconds removed from that datetime. For the most part it doesn’t matter, but there could be some cases where you are comparing that date to another date and the time difference may cause an issue.
Also, the template column is a good trick, but only as long as you are OK with the date being text. It’s no longer a date.
I guess it wasn’t clear what your use case is, so maybe we all overcomplicated what you needed.
@Jeff_Hager Again, thanks.
I always tell people to tell me as much about the problem as possible - to be able to help. I didn’t do that well
I do a look-up in a bookings table. Here I need a person and a date ‘MrX11. januar 2023’ (ok, in danish). Here I cannot accept any time like hours, minutes, seconds.
The way template column handles date&time column might just be lucky as it takes away hours, minutes, seconds. I hope it will keep working like that - otherwise I will be in trouble
Hehe, that’s the battle. At least you are willing to share. The problem is when you ask for more information and some people still won’t tell you much or blatantly ignore your questions.
Our bad for not asking you more in the first place.
Yeah, I think what you have will work just fine. You’re just using the template column to stabilize the date so it works better for relations and lookups.
Gorgeous. I have no idea what the trunc function does, it’ll be a good opportunity to learn about it tomorrow.
I love this. I don’t think the seed date is much of an issue. It’s a little arbitrary like the +15 we’ve gotten used to to stay clear of the edges of a month, and if it serves it’s purpose then so be it.
The challenge for me will be to remember.
I just looked it up. This is so smart.
TRUNC is pretty much the same as FLOOR. Basically TRUNC just chops off anything after the decimal point. FLOOR always rounds down to the nearest whole number. Essentially they do the same thing, and that’s to remove anything after the decimal point.
In comparison to FLOOR, you also have CEILING, which always rounds up to the nearest whole number. This is in contrast to ROUND which can round up or down to the nearest whole number. But ROUND has the added benefit of being able to specify how many decimal places you want to round to.
If TRUNC and FLOOR do the same, I wonder then why they would be different functions? Perhaps they act differently with negative numbers? I’ll look it up.
Interestingly, there is a difference.
Math.Floor rounds down,
Math.Ceiling rounds up, and
Math.Truncate rounds towards zero. Thus,
Math.Truncate is like
Math.Floor for positive numbers, and like
Math.Ceiling for negative numbers. Here’s the reference.
That’s make total sense. I was wondering if maybe FLOOR rounded towards zero and CEILING towards infinity, but it’s the other way around. TRUNC brings you closer to zero whether positive or negative, it’s kind of obvious if I think of it.
Now I’m wondering, isn’t
now-((now-seed)-TRUNC(now-seed)) equal to
TRUNC(now) doesn’t return a duration or a date. It returns the google format of a decimal date, which is actually convenient for some things, but not what I needed here. What I needed was only the time portion of the date, which is anything after the decimal point. I needed it so I can subtract it from the current date time to get midnight of the current date.
First I needed to get a duration between now and a seed date
(now-seed) just to get a decimal duration, then subtract the truncated duration of now and the seed date `TRUNC(now-seed)', which is duration without time, to ultimately get a duration with only time. This is just the decimal remainder (hours/minutes/seconds/millisecond). Then I subtract that from Now, so it will always result to midnight of the current day.
Yes, all the time.
But, I missed a column in that solution that I gave you - oops
THIS is how I would do it:
- Math column to return todays date
- Math column to add the row index to todays date
- Math column to convert to YYYYMMDD
This approach deals with month/year boundaries just fine.
Sorry about that, I’m not sure what I was thinking yesterday
It should be, but you could potentially come unstuck by device regional date formatting issues, especially if you have Dates saved as text anywhere.
Personally, I think the “convert-to-integer” approach is a more robust approach. I’ve never known that to fail.
(as long as it’s done properly, of course)
I understand the formula very well. From your explanation, it’s seems TRUNC acts differently when applied to a difference between dates than applied to a date only. Intuitively, since Date is the integer and Time the float (at least this is my understanding), then I would have expected TRUNC(DateTime) to be Date (at midnight).
I will hit the sack, ponder this, and experiment tomorrow morning. Thank you Jeff for your explanations.
Darren, we cross paths, good morning!
Yes, that is definitely true, and Trunc(Now) is essentially the date at midnight, but the result for today is
44966 which can be converted back to a normal date in Google, but there is no function in Glide to convert it back to a date. 44966 is the number of days since Dec 30th 1899. That’s not a number that’s useful for me to subtract a few hours.
I guess an easier alternative would be to set the seed date to Dec 30th 1899 at midnight and then change the math column to
seed+TRUNC(now). Probably should have thought of that instead. That makes a lot more sense.
I wish Glide had functions in the math column to convert text to date, similar to Google’s DATE and DATEVALUE functions. Then we wouldn’t need some of these extra columns to make these functions work.
(Sorry guys, I’m living in the past. Feb 9th really is today for me. )
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.