I’m trying to create a query based on a date, but I’m getting inconsistent or weird results from various date functions. Both the “relative date” function and the math column (two examples of that latter below) return dates or times in which there is a 24 hour discrepancy for dates that aren’t the current day and I can’t figure out why.
Is it an issue with the underlying data?
Not sure I’m following completely, but your date math does include time, and based on your location, the difference between bow and yesterday at 12am is approximately 2 days, whereas the different between now and today at noon is less than 12 hours ago. Not sure what you are going for, but the results look correct to me. Also note that Relative time is approximate.
Disregard my poor maths skills. But what I want is on Tuesday for Monday to be 1 day ago, but it displays as 2 days ago. I can’t control the data for the date as it comes from another source but is there a way to format it so that Monday is “1 day ago” if it’s Tuesday until it is actually 2 days ago I.e Wednesday at 12 am.
I would convert both the date in question and “today” to integers using the following formula:
Year(Date)*10^4
+ Month(Date)*10^2
+ Day(Date)
and then you can do a straight numerical comparison.
That won’t work well if subtracting 20241231 from 20250101, which results in 8870 instead of 1 day. I would use this instead to convert dates into numbers, excluding time.
TRUNC(Date)
oh yes, good point 
This is great! Thank you both. That works.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.