Military Time Import
By Richard Rost
3 years ago
Import Spreadsheet w Military Time into Access
In this Microsoft Access tutorial I'm going to teach you how to take a spreadsheet that has military times in it such as 1400 and 2230 and import those into Microsoft Access as proper time values.
Samantha from Jacksonville, Florida (a Platinum Member) asks: I am using Microsoft Access and I have received a spreadsheet that has times in military format such as 1400 and 2230 and so on. Is there any way I can import this data into Access and convert those values into regular times in a date/time field?
Pre-Requisites
Recommended Courses
Formulae
- X: Format([MyTime],"0000")
- H: Left([X],2)
- N: Right([X],2)
- NT: TimeSerial([H],[N],0)
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, ms access import military time to date/time, excel 24 hour time to access, military time, date time, time values, time format, import Excel to Access, update query, time serial, query design, short text, join tables, date math
Intro In this video, I will show you how to import military time values, such as 1420 or 2230, from an Excel spreadsheet into Microsoft Access and convert them into proper date/time fields. We will cover working with text imports, using the Format, Left, and Right functions to separate hours and minutes, and applying the TimeSerial function to create valid time values for your Access tables. This tutorial is intended for users who are comfortable with Access basics and want to learn best practices for handling 24-hour formatted times without using any programming or VBA.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I'm going to teach you how to take a spreadsheet that's got military times and 24-hour times, like 1420 and 2230, and import those into Access as proper date/time values.
Today's video is an expert-level video. Expert is something I put between beginner and developer, so there's no programming in this video, no VBA or anything, but you have to be beyond the basics because we're going to cover some functions, some importing, and some other cool stuff.
Samantha from Jacksonville, Florida says, I am using Microsoft Access and I have received a spreadsheet that has times in military format, such as 1420 and 2330 and so on. Is there a way I can import this data into Access and convert those values into regular times in a date/time field?
Before we get started, we have some prerequisites today. If you don't know how to import data from Excel, go watch this video.
If you want to learn more about working with military 24-hour time in Access, go watch this video. In this one, I teach how to actually enter military time into Access, which is a whole different ball game from importing it from Excel, but this will give you some background.
Go watch this video on the Format function. We're going to use this guy.
We're going to use two string functions, the Left and Right string functions. Go watch this.
I want you to go watch my DateSerial video. This is how you can take the components of a date, like the month, date, and year, and put them together to make a date value. We're not going to use DateSerial in this video. We're going to use TimeSerial, which is the same thing. You put the hours, minutes, and seconds together to make a time value, but I do not have a separate TimeSerial video. Go watch this one. It's the same thing. It just works with the parts of a time instead of the parts of a day. I have to make a TimeSerial video, I know. Go watch this one.
And finally, we're going to use an update query to update the value in our table. So if you don't know how to do an update query, watch this video.
All of these are free videos, by the way. They're on my website and they're on my YouTube channel. Go watch those now if you don't know any of those things, and then come on back.
Here's a copy of what Samantha has. I scaled it down and removed a bunch of information that doesn't make a difference for this video. Basically, she's got a list of names and a list of time values. These time values she's stored in Excel and they're stored as numbers. It doesn't matter if it's numbers or text for this example, but that's what she's got. She wants to get this into Access and then have these stored as actual time values. Once it's a time value, you can do stuff with it - you can add minutes, subtract hours, whatever you want to do. You can get a regular date/time value.
Let's go over to Access.
Let me show you what people try to do that usually doesn't work. Go to External Data - New from File - Excel. Browse to wherever your source file is. Mine's in my drive and then spreadsheets.
There it is. Import. Hit OK.
First row contains column headings. Yes. Next.
Now you know me. I don't like 'name' or 'time' as field names. I want to change this to my name, leave it as text, and then my time.
What a lot of people try to do is they try to change this to Date/Time. This is where the problem comes in. Hit Next. Add a primary key. That's fine. Sheet1 is fine. Then Finish.
Open Sheet1. There. Those are not the right times. What happened was Access imported those values, but instead of importing them as times, it imported them as dates. In date values, a value of 1 is a whole day, so it actually said, okay, that's the day - 1430. Day zero in Access is December 30, 1899. Why? It's a long story.
What it did was it added 1430 days onto December 30, 1899. That's the value that you got. We do not want to import this as a date value. We want to import this as a time value.
Now, you can play some tricks with math, but honestly, the easiest way to do this is to import this as text and then pull it apart that way. That's the method that I like to use, and that's what I'm going to show you now.
Delete this table. Get rid of it. Goodbye.
Let's try that again. Import from file - Excel. Browse. Get my sheet. OK.
Same thing. Next. We're going to change this to my name, short text. Then we're going to click on this one, and we're going to call this one my time, but this time import it as short text. OK.
Next. Primary key is fine. Next. Import the table. Let's call it my time t, because we're going to keep this one.
OK. Now take a look at what you have. There you go. It brought them in just the way we see them in Excel. That's fine.
Now we're going to make a query and massage this a little bit.
Create - Query Design. Bring in my time t. All right. Bring everybody in.
It looks just like that so far. Now what we're going to do is we're going to format this so it's always four characters, because if you have 930 right there, you're missing the leading zero - the 'oh' as the military folks say - so it's always formatted as four digits.
I'm going to Shift+F2 to zoom in so you can see this. We'll just call it X. It's going to be Format([my time], "0000"). So it's always going to be four digits.
If I run that, now you can see this is always a four-digit number. Actually, technically, it's text. You can tell because it's lined up on the left side. If it was a number or a date value, it'd be lined up on the right side. That's a little trick for you.
Now that we've got this as always four characters, we can break that apart into its hour and minute.
Come in here, and again, I'll zoom in. H is going to be the Left(X, 2) - give me the left two characters.
For minutes, the next field, we're going to make it N, because in Access, remember, M is month, so N is going to be minutes. That's going to be Right(X, 2).
Now we'll take a peek. Look at that. Everyone looks perfect. 1350 is down there. 0930 is up there.
Now we can use TimeSerial to build a time value - an actual date/time value - out of these two components. That's going to look like this. We'll call it NT for new time. It's going to be TimeSerial(H, N, 0).
If you do have times that have all six digits, then just break this up accordingly - you'll use Left, Mid, and Right.
Most military times that I see are usually hours and minutes. They don't usually put seconds.
If I run that, there you go. There's an actual valid Microsoft Access time value based on hours and minutes from the military times. 1430 is 2:30 pm.
Save this as new time q - there's your new time query.
Now, you don't want to have to run this query every time unless you want to. That's up to you, but if you want to actually store that value in your table, you can use an update query.
Now you have to make a second query. You can't just take this guy and turn it into an update query, because these are calculated values and those have to get calculated before this can run.
We can make a second query. First, we have to add a field to your table to store that. Call it new time, and that can be an actual date/time value. If you want, you can format this as Short Time, or you can format it as Medium Time if you want to see it like that.
Save that. Now create another query. This will be our update query. We have to join the other two together, though. Bring in my time t and our new query, new time q. They should be linked by the ID.
Make it an update query, and we're going to update new time to what? To NT.
Come down here. Where it says Update To, don't just type in NT, because it's going to put it in quotes and you don't want that. You want the field NT. This is one of those times where you want to put brackets around it. I'll zoom in so you can see a little better, so you can make sure you get your NT.
We're going to update new time in the time table to the NT field, which is in the new time query. This guy that we calculated before - since these were joined by the ID - it should work just fine.
I'm going to save this as time update q. If you want to run it again, if you have to import this stuff on a regular basis, you're going to keep these things around.
Run it.
Nothing appears to happen because I have my warnings turned off. I explained that in the update queries video. I hate those warnings that are annoying.
Open my time t and there's the value in your table now. That is how you do that.
If you want to learn more about this date/time stuff, I cover lots more in my Access Expert 27 and 28 classes. We talk about date/time functions, all the different date stuff. It's all really cool.
I also have this nifty thing called the Date/Time Seminar, where I go over pretty much everything there is to know about working with dates and times in Access - how to calculate work days and reminder pop-ups and how to figure out days with holidays and everything. Everything I know about dates and times, that's what this Date/Time Seminar is for. I'll put a link to this down below.
Now, if you've been following my TechHelp videos in the recent past, you know that I've been playing with ChatGPT and Bard and Bing and all the different AI stuff that's out there to see how they handle my problems that I come up with and that I answer for people in the TechHelp videos.
I am sad to say that ChatGPT and Bard both failed this one miserably. I literally fed in the exact question that I used for this video, and it... no. It basically said to import it, pick date type, date/time, and set the format for a time, and that is completely incorrect. I didn't even bother trying to fix it. I just gave up.
Then I went over to Bard, which is Google's version of AI, and it gave me all kinds of weird stuff. I don't even know. No.
It did get one thing correct, though. I was not sure off the top of my head what day zero was. I couldn't remember day zero. It's December 30, 1899 if you put 0 in as a date value. I said, what is day zero? I'm like, it's not that. It gave me all kinds of weird stuff. Then I tried to get it mathematically, because I know 1430 from the spreadsheet, and it tried to tell me, at one point in here, that there's, you know... I said, what's November 30, 1903 minus 1430 days? It was giving me another day in 1403.
See, no. I said, that can't be right since there are 365 days in a year. Then it apologized and still gave me the wrong answer. I went back over to Bard and asked it that question. I said, what's 1430 days before November 30? It gave me the right answer. At least it got its date math correct. ChatGPT, you failed miserably on both of those things.
It just goes to show you, folks, you still have to take this AI stuff with a grain of salt. It's helpful, but it's not authoritative yet.
Anyway, that is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends, and I'll see you next time.Quiz Q1. What is the main challenge when importing military/24-hour time values like 1420 or 2230 from Excel into Access?
A. Access automatically converts these numbers to text, which cannot be formatted
B. Access imports them as date values, which represent incorrect dates
C. Access changes all times to 12-hour format during import
D. Access ignores these values and leaves fields blank
Q2. Why is it not recommended to change the field type to Date/Time during the Excel import process?
A. Because Access will automatically discard non-date values
B. Because Access will correctly convert them to date time values
C. Because Access interprets numbers as days and produces unintended dates
D. Because Access cannot import any date information from Excel
Q3. What is the recommended field data type for the military time values when importing from Excel into Access?
A. Date/Time
B. Number
C. Short Text
D. Boolean
Q4. After importing the military time values as text, what is the first formatting step suggested?
A. Convert all times into 12-hour format
B. Add a leading zero so all values are four digits
C. Sort all times in ascending order
D. Convert the column to numeric data type
Q5. Which Access function is used to ensure each time value is always four characters long (e.g. "0930" instead of "930")?
A. Padding([my time])
B. Format([my time], "0000")
C. Len([my time])
D. Replace([my time], "0", "")
Q6. Which two string functions are used to extract the hour and minute parts from the 4-digit formatted time?
A. UpCase and DownCase
B. Split and Concat
C. Left and Right
D. Mid and Replace
Q7. In Access, which letter is used for representing the 'minute' component in queries/function expressions?
A. M
B. Min
C. N
D. T
Q8. What function is used in Access to create a valid time value from hour and minute components?
A. DateSerial
B. TimeSerial
C. FormatSerial
D. MakeTime
Q9. Why do you create a new field in your Access table after calculating the proper time value in a query?
A. To store it as text for later use
B. To keep a backup of the original data
C. To store the calculated time as a real Date/Time value
D. To create a lookup to external tables
Q10. Why do you need to create a second query (an update query) after calculating the new time value in a select query?
A. Because you cannot save calculated fields directly to the table
B. Because Access does not support calculated fields in any queries
C. Because select queries update values automatically
D. Because update queries require macros
Q11. What is the correct method to reference a calculated field (NT) from a previous query in your update query?
A. Type NT without brackets
B. Type "NT" in quotes
C. Enclose NT in brackets like [NT]
D. Prefix NT with a colon :NT
Q12. Why did common AI tools like ChatGPT and Bard initially fail to answer the import/conversion question accurately?
A. They recommended table joins instead of conversions
B. They advised importing as Date/Time, leading to incorrect results
C. They did not recognize Excel as a valid data source
D. They suggested manual entry of times
Q13. What is the significance of 'day zero' in Access date values?
A. It represents the first day of each month
B. It is the default date for all new records
C. It is December 30, 1899, used as the starting point for date calculations
D. It is January 1, 1900, the same as Excel
Q14. What is one advantage of storing imported times as Date/Time values in your Access table?
A. You can perform date and time arithmetic operations
B. You avoid the need for queries
C. You automatically convert to time zones
D. You can import data faster
Q15. If the imported time values from Excel included seconds (six digits), what function(s) would you use to extract hours, minutes, and seconds?
A. Only Left and Right
B. Left, Mid, and Right
C. Split only
D. Format only
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-A; 11-C; 12-B; 13-C; 14-A; 15-B
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 TechHelp tutorial from Access Learning Zone covers how to import military or 24-hour formatted times from an Excel spreadsheet into Microsoft Access and convert them into valid date time values.
I often get requests from students who need to handle time data coming in as 4-digit military style numbers, such as 1420 or 2330, rather than as standard date/time values. In this lesson, I will walk you through the process of importing these times as text and then processing them within Access so they become usable time fields.
This lesson falls within my "expert" category, which is the level between beginner and developer. We're not doing any programming or VBA today, but some familiarity with Access functionality and functions is required. I am going to make use of a few built-in Access functions along with import features and update queries.
Let's talk about prerequisites for today. You should already know how to import data from Excel into Access. If you need a refresher, I have a dedicated video for that. It's also helpful if you're familiar with working with military or 24-hour time in Access, though entering these times manually is a different process from importing them in bulk, as we're covering here.
We will be using the Format function to consistently format our data, and both the Left and Right string functions to extract hours and minutes from our text. If you aren't comfortable with these functions, I've got tutorials for each. Additionally, while I don't have a video strictly dedicated to the TimeSerial function, it is quite similar to DateSerial, which lets you create a date value from its component parts. TimeSerial works the same way but with hours, minutes, and seconds. If you're unfamiliar with building values from pieces this way, my DateSerial video is a recommended watch for background.
Finally, this process will require an update query. If update queries are new to you, be sure to brush up on them first using my video guidance.
Now, let's get into the specifics. For this example, imagine a simplified worksheet: a list of names, each with a corresponding four-digit military time. The times are stored in Excel as numbers or text, but that distinction doesn't matter for our purpose here. The goal is to bring them into Access and convert those to proper time values, making them usable for calculations, formatting, or any standard date/time operations.
Many people start by simply importing the spreadsheet into Access and trying to assign the time column to the Date/Time data type during the import process. This, however, usually doesn't work as expected. Access interprets those numbers as a count of days since a base date of December 30, 1899, not as hours and minutes. The resulting values end up as odd dates, not times.
To avoid this issue, I recommend importing that time data as text. That way, the data arrives in Access exactly as it appears in Excel, and you can process it safely. Once you have the table created with the times as short text, you can begin manipulating the data with queries.
Create a query that includes all your fields. The first thing you should do is ensure that every time value is formatted with four digits. For example, 930 should display as 0930. You can accomplish this using the Format function on the time field. This results in a string that always has four characters, which makes it easy to extract hours and minutes.
Next, use the Left function to grab the first two characters, which represent the hour, and the Right function to grab the last two characters, which represent the minutes. Now you have both components needed to build a time.
At this point, use the TimeSerial function, feeding it the hour and minute you just extracted, along with zero seconds. This function returns a valid Access time value.
If you later encounter times that include seconds (six digits), you can modify the parsing logic to accommodate that as well, splitting out hours, minutes, and seconds accordingly.
Once you see correct time values in your query, save it for reuse. However, if you would like to actually store the converted times in your table for sorting or calculation, you'll need to take it one step further. Add a new field to your table with the Date/Time data type designated for the new time. Then create an update query that copies the calculated time value from your conversion query into this new field within your main table. You'll typically join the two queries together by the unique ID to ensure each time value is properly matched and updated.
After the update query runs, you'll see the new time values stored right in your table, formatted however you wish, such as Short Time or Medium Time.
If you want to continue learning more about working with dates and times in Access, I cover a lot more in my Access Expert classes 27 and 28, which deal with many date and time functions and tricks. I also offer a Date Time Seminar, which goes into every aspect of handling dates and times in Access, including workday calculations, reminders, and more.
As an aside, I have tested how some of the current AIs like ChatGPT and Bard handle this type of problem. Unfortunately, the results were not very helpful. The AI assistants either gave incorrect methods or confused the underlying date math entirely. So, while AI can be useful for some tasks, take its advice with a grain of salt. For situations like this, a more hands-on approach is best.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Importing Excel data with military time to Access
Issues with importing military time as date values
Importing military time as text to Access
Formatting imported times as four-digit strings
Using the Left function to extract hour values
Using the Right function to extract minute values
Building time values with the TimeSerial function
Creating a query to convert military time to date time
Adding a new date/time field to the Access table
Updating table records using an update query with joined tables |
