# Date Formatting Causing Sorting Error?

**URL:** <https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718>\
**Category:** Ask for Help\
**Tags:** data-sync\
**Created:** [May 28, 2024, 3:24pm UTC](https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718 "2024-05-28T15:24:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Learned1](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/andrew_learned1/32/55381_2.png) [@Andrew\_Learned1](https://community.glideapps.com/u/Andrew_Learned1)\
**Post date:** [May 28, 2024, 3:24pm UTC](https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718/1 "2024-05-28T15:24:22Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [May 28, 2024, 3:34pm UTC](https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718/2 "2024-05-28T15:34:36Z")

</div>

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:

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

```

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

---

<div class="post-metadata">

**Author:** ![Andrew\_Learned1](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/andrew_learned1/32/55381_2.png) [@Andrew\_Learned1](https://community.glideapps.com/u/Andrew_Learned1)\
**Post date:** [May 28, 2024, 3:43pm UTC](https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718/3 "2024-05-28T15:43:33Z")

</div>

Thank you!

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [June 4, 2024, 3:44pm UTC](https://community.glideapps.com/t/date-formatting-causing-sorting-error/73718/4 "2024-06-04T15:44:07Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.