We ran into a scenario where several people want to be select the exact minute in the date/time fields. The OOB field only allows increments of 5 minutes (there is a check to make sure it’s divisible by 5). Many workarounds are shown on the web to create choice columns for Date, Hour, Minute, and AM/PM. Then create a calculated column to create a Date/Time field. This works except when you want to sort. The issue is with the way the calculated field is created in these posts.

Columns

Date – Date only field

Hour – Choice Field – 1, 2, 3…12

Minute – Choice Field – 00, 01, 02, 03, … 59

AM/PM – Choice Field – AM, PM

Strings vs Date/Time types

Sorting strings (single lines of text) will sort differently than Date/Time types. Let’s take an example the following dates.

01/21/2021

02/21/2019

03/18/2017

The above would be sorted based on the String type since we sort left to right. Sorting based on Date/Time would be Year->Month->Day->Hour->Minute->Second

03/18/2017

02/21/2019

01/21/2021

Calculated Column

The formula you would see would add in the columns above to get a string but the format looks correct

=TEXT(([Date],”mm/dd/yyyy”)&” “&[Hour]&”:”&[Minute]&” “&[AM/PM])

Example of what this would look like:

01/21/2021 1:21 AM

This will be treated as a string since we have =TEXT

Solution

=IF(AM/PM=”AM”,[Date]+([Hour]/24)+([Minute]/24/60),[Date]+(([Hour]+12)/24)+([Minute]/24/60))

Instead of adding things to a string we will use math to add the time into the date. The formula can only accept the increments of days so 1 AM would be 1/24 of the day. Minute would be 60 minutes in every hour and 24 hours in every day.

Hour/24

Minute/60/24

PM we would add 12 hours.