12 Hour Time with NO am/pm
By Richard Rost

6 years ago

Display Time in 12-Hour Format Without AM/PM

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this Microsoft Access TechHelp video, I will show you how to take a time value and put it in h:nn (1:30) format WITHOUT the am/pm but still showing in 12-hour time format. The questioner is working for a school, so she doesn't have to worry about 1am or 11pm times.

Members

There is no Extended Cut for this video.

Links

Time Formats
Format Function
IIF Function
Left Function
Report Sorting

Learn More

FREE Access Beginner Level 1
FREE Access Quick Start in 30 Minutes
Access Level 2 for just $1

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Diamond Sponsors - Information on our Sponsors
Mailing List - Get emails when new videos released
Consulting - Need help with your database
Tip Jar - Your tips are graciously accepted
Merch Store - Get your swag here!

Questions?

Please feel free to post your questions or comments below or post them in the Forums.

Comments for 12 Hour Time with NO am/pm

Age	Subject	From
3 years	Calculation	Giovanni Miranda

Subscribe to 12 Hour Time with NO am/pm
Get notifications when this page is updated

Intro

In this video, I will show you how to create a 12-hour time display in Microsoft Access without showing AM or PM, which is helpful when you only need simple time ranges like "830-130" for things like school schedules. We will format start and end time fields, convert them to short hour-minute text values, remove the AM/PM notation, handle leading zeros, and build a query and report to display and sort these times correctly. If you want to see how to set up a clean, readable time range for your Access reports without switching to 24-hour format, this tutorial is for you.

Transcript

Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost and in today's question, we are going to show you how to do a short time value without the AM or PM, but also stay in 12-hour format.

Why? You will see why in just a second.

Today's question comes from Julie. Julie says, "I have start time and end time fields in my table with a medium-date format and I have concatenated them in a query to look like this: start time dash end time. The following result looks good, but I need it to display as 130 to 230 in a report. I know I can format to HMM. It is actually H:NN; M is for month, N is for minutes. But is there a way I can get a short version like this without getting 24-hour time? Because as soon as you do that, 130 PM becomes 1330. Also, the problem is when you try to format the fields like that, you get it stuck where the 130 comes before the 830. So if you have a class that starts at 830 in the morning and ends at 130 PM, it is going to get sorted wrong."

She says, "I am working with school times 9 to 3, so there are no 1 AM or 830 PM times." This is a case where you might want to do it in a short version like that without the AM or PM because you never need it.

Here is a simple table. We have a class name, a start time, and an end time. These are just the raw data in the table: 830 AM to 1 PM, 10 o'clock AM to 2 PM, 1 PM to 3 PM, and so on.

Here is a query: class, start time, end time. If you run it, that's what it looks like with no formatting on the times.

First, let's get these formatted as just hour and minute with the AM and the PM. Go to design view. I am going to set up ST: start time is format the start time field as just H: and remember, N is for minute, M is for month. I am going to put the AM/PM at the end of it like that. I will do the same thing with end time in just a second.

There we go. There is our short version with AM and PM. Let's do the same thing with end time. I will just copy and paste that column there. This will be ET and end time.

These are a little more manageable now.

If you are okay with that leading zero, this will be really easy to do. All we have to do is convert this over to text, which Access will do automatically for us, and just chop off the right three characters - the space PM or the space AM. So what I need is the left five characters there. That is why I said, if you are okay with that leading zero, then this gets real easy.

Shrink these down. Let's say ST2 is going to be the left of ST, five - the left five characters. If I run that now, look how easy that is. If you are not worried about the AM PM, then that is easy. Do the same thing here with end time. So ET2 is the left of ET, five. Run that and there you go.

Now, here is the thing: these are text values. If you sort them, it is not going to give you a proper date-time sort. You need to leave this value in your report and also bring in this. Base the report off of TimeQ now, the query. Use this for your sorting and then use this for the actual display. You can do both. You can sort based on one field and display another one.

If you really want to get rid of that leading zero, you can take a look and see what it is, and if so, make the adjustment. Now, you can put this all inside of one column if you want to inside of one calculated field. I like breaking it up though. It makes it easier to understand.

Right over here, we are going to say ST3 is like this: If the left of ST2, 1 equals a zero, then this value is going to be the right of ST2, four. Otherwise, just leave it as ST2.

Now, let's take a peek. There you go. Basically, we are saying if the leftmost character here (left 1) is a zero, then make this the right four characters. Otherwise, leave it alone. That is the IIF function (Immediate If). I will put links to lessons that talk about all these other functions in the description field below the video.

Let's do the same thing here with end time. So ET3 is going to be the left of ET2, 1. I almost did one too many. There we go.

Now we can put this all together into one field just the way you want it. We come over here and say "class times" is going to be ST3 & "-" & ET3, just like that. Run it. There you go. There are your class times.

This is for display purposes only. When you make your report, you are still going to sort based on start time and end time or start time, or however you are going to sort the report. Just bring this whole query in as the record source for your report. Then you can display that and sort by that.

For a basic report, just go to Create, then Report Design. I like building stuff from scratch. I stay away from the wizards.

Set the record source, which is going to be my TimeQ that I just created. You have to base it on the query so you get all those values in it. Add existing fields. What do we want to see in the report? I want to see the class information and I want to see this class times field that I created for the display.

Take these labels, chop them off, put them up here in the page header. Slide your class over here, put your class times next to it. Grab that bottom here, slide it up. I cover all of this in my lessons on how to build reports.

Save this real quick as MyTimeR or MyTimeReport. If I close it and take a look at it, let's say we are sorting by start time. My values in the table just so happen to work for now. But notice now we are getting that time sort this way. You are seeing the values as they appear in the table.

Let's change things up a little bit. Let's go to PM to 4 PM. Notice this is adjusted accordingly. If I open up my report, there is still no particular sort order. Let's add a sort in design view.

How do we add a sort? Go to Grouping and Totals, Grouping and Sorting, and that turns this little bottom thing on. Add a sort. What do you want to sort by? Start time. These are the fields in the actual table: start time and end time. So pick start time. That will sort now from oldest to newest based on start time.

You can close this little value thing here if you want to. Now, shut down the time report and open it back up again. Now notice that we are sorted based on start time, and it is the actual time value in the table. Math is first at 10 AM, followed by gym at 1 PM, followed by English.

That is how you do it. That is how you can basically create any kind of look you want with a query if you know the text manipulation functions and how to do Left and Right and Mid and all that stuff. I teach all that in my classes and I will put links below.

Then you make your report. You just have to remember to sort on the original value and display whatever you want. You could also do all that formatting inside the report itself. I prefer doing it in an underlying query, but you can do all that stuff right inside of a field inside the report. Queries are just easier.

Thanks for watching. I hope you learned something. If you enjoyed this video, make sure you subscribe, click on the little bell, and select all. You will get notifications whenever I post new videos on YouTube.

If you are watching this video on my website, make sure you subscribe to my Access Forum. There is the link and you will get email notifications every time I post something on my website.

Got a question you would like to see answered? Post it on my website, post it on YouTube, or post it on my TechHelp page. If I like it, I will make a video just like this one. Of course, you can always email me. There is my direct email address, there is where you can find me: my blog, Facebook, Twitter, YouTube, all that stuff.

Shameless advertising: If you like this video and you want to learn more Access, you can watch my free Level 1 class. It is three hours long. There it is. Three hours long. It is a three hour tour. It is on my website right there. It is also on YouTube. Follow that link: Free Level 1. If you like Level 1, you can get Level 2 for just a dollar. That is a great deal. It is another hour-long class.

Again, thanks for watching and take care. Keep learning.

Quiz

Q1. What was Julie's main formatting requirement for her class time report in Access?
A. To display class times as short 12-hour format without AM or PM
B. To convert all times to 24-hour (military) time format
C. To include full date and time stamps
D. To display class times only in seconds

Q2. When concatenating start and end times in Access, what happens if you use the H:NN format and leave out AM/PM?
A. You risk converting to 24-hour time and mis-sorting afternoon times before morning times
B. Access automatically displays times in chronological order
C. The format always retains AM/PM internally
D. The concatenated string is always sorted correctly

Q3. How did Richard suggest extracting the hour and minute without AM/PM for display purposes?
A. By taking the left five characters from the formatted time string
B. By using the Mid function to extract minutes only
C. By concatenating start and end time fields with a slash
D. By multiplying hours by 100 and adding minutes

Q4. What potential sorting issue arises from displaying time as text in the report?
A. Sorting by text values may not sort the times chronologically
B. Access will automatically add a leading zero
C. Text values sort correctly by default in all cases
D. The date is lost in the conversion

Q5. What is the purpose of using the original start and end time fields when making your report?
A. To ensure proper sorting based on actual time values
B. To display seconds in the report
C. To show the leading zero in each time value
D. To hide the report headers

Q6. What Access function did Richard use to remove the leading zero from time values like '0130'?
A. IIF function with Left and Right string manipulation
B. DLookUp function on the source table
C. Sum function in the query
D. FormatDate function

Q7. Why does Richard prefer performing the time string formatting in the query rather than in the report?
A. Queries are easier to understand and maintain
B. Reports cannot handle formatting at all
C. Query formatting does not affect report layout
D. It is impossible to do string manipulation in reports

Q8. What does the "Grouping and Sorting" option in the report design allow you to do?
A. Sort the report based on a specific field like start time
B. Add conditional formatting to report headers
C. Automatically generate new tables
D. Hide report labels

Q9. According to the video, how can you display a formatted time range like '830-130' but still sort by the actual time?
A. Use the formatted string field for display and the original time field for sorting
B. Sort by the formatted string field only
C. Use only the original time values for both sorting and display
D. Sort by report headers

Q10. What is the significance of using the LEFT and RIGHT functions in the query?
A. To extract portions of a string for custom formatting
B. To sum time values
C. To convert a string back to date/time format
D. To create relationship links between tables

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A;

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on how to display short time values in Microsoft Access in a 12-hour format, omitting the AM or PM, while keeping the times clear for display in reports. The question comes from someone who works with scheduling school classes, where all times fall within the school day and there is no ambiguity between AM and PM.

I start by explaining the challenge: typically, if you format time as H:MM, Access gives results like 1:30 for 1:30 AM or PM. If you use a 24-hour format, then 1:30 PM becomes 13:30, which is not what is needed. The goal here is to show times such as 130 to 230, without showing AM or PM, in a way that is suitable for things like school schedules.

First, I set up a simple table with fields for the class name, start time, and end time. These fields contain standard time values like 8:30 AM or 1:00 PM. When viewing this table in a query, the times display in their unformatted state.

Next, I demonstrate how to format start and end time fields in a query to show just the hour and minute with AM or PM. Using the Format function, I show how to transform the times into a short format with AM/PM at the end. This makes the data more manageable, but the next step is to remove the AM or PM part for the purposes of the report.

To do this, I convert the formatted time into text and use string manipulation functions to chop off the last three characters, which are always " AM" or " PM." This gives me a field that displays just five characters, such as "08:30" or "01:30." I repeat this process for both start and end time fields.

However, removing the AM or PM also introduces a new issue: these new values are now text rather than time values, so sorting them may not reflect the true chronological order. To solve this, I stress the importance of still including the original time fields in the query, so you can sort by the underlying true start or end time, even if you display the custom text field in the report.

For even more concise display, if you do not like the leading zero (for example, "08:30" for 8:30), I show how to use the IIF function in Access to remove it. Essentially, if the first character is zero, I take only the right four characters.

Once I have the final formatted versions of the start and end time fields, I concatenate these with a dash between them to create a single field like "830-130" for display purposes. This makes it easy to present class times in a compact and consistent format on reports.

When building the report, I use the query as the record source and include both the original time fields (for sorting) and the custom display field (for showing on screen or prints). Sorting is set based on the start time field to ensure that the report lists classes in their correct order through the day.

Throughout the process, I discuss the value of breaking the formatting logic into separate fields for clarity, but I also mention that it's possible to combine all of the logic into one calculated field if you prefer.

Before wrapping up, I remind viewers that while you can also handle all this formatting directly in a report's controls, it is generally clearer and more manageable to keep the formatting logic in the underlying query.

For a complete video tutorial with step-by-step instructions on everything covered in this article, visit my website at the link below. Live long and prosper, my friends.

Topic List

Formatting time fields to 12-hour format without AM/PM
Using Format function to display hour and minute
Creating calculated fields for custom time display
Converting time values to text for display
Removing AM/PM from formatted time strings
Eliminating leading zeroes from time strings
Combining start and end times into a single field
Sorting records based on original time fields
Setting up a report to display custom time formats
Base report record source on a formatted query
Adding and configuring sorting in Access reports
Using Group and Sort in Report Design view