Date Formatting Causing Sorting Error?

[Andrew_Learned1]

Hey y’all! I think I’ve seen something like this before but couldn’t find the thread. I have a method for running payroll where after each day the employees clock in/out time is assigned to a paydate. The paydate is a lookup column in the format: 5/31/2024

Then, to calculate the hours I have a Query that looks if the shift’s paydate is the next paydate for the user… I hope that makes sense…

But I’m having an error where some shifts go missing. When I look at the original date/time column where we assign the paydate where the format is again MM/DD/YYYY it all looks correct UNTIL I change the format to include time and realize some are AM and some are PM like so:

Friday, May 31, 2024 at 12:00 AM
Friday, May 31, 2024 at 12:00 PM

Do y’all know any ideas how to force the format of the input to not include the time if we don’t want it. Is that even my problem? Any other ideas you can think of?

[Darren_Murphy]

You should never rely on date formats for things like queries and relations, because date formatting can and will vary from device to device.

Convert your dates to a numerical representation of the date in YYYYMMDD format, using the following math formula:

Year(Date)*10^4
+Month(Date)*10^2
+Day(Date)

This will give you a consistent and reliable format to use in your query.

[Andrew_Learned1]

Thank you!