0:00:00
Welcome to another Tech Help 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 in it 24-hour times like 1400 and 2230 and import those into Access as proper date time values. Today's video is an expert level video. An expert is something I put between beginner and developer so there's no programming in this video no VBA or anything but you got to be beyond the basics because we're going to cover some functions and some importing and some other cool stuff. Samantha from Jacksonville Florida says I'm using Microsoft Access and I have received a spreadsheet that is times in military format such as 1400 and 2230 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?

0:00:58
Before we get started, we got some prerequisites today. If you don't know how to import data from Excel, go watch this video. If you wanna learn more about working with military 24 hour time in Access, go watch this video. This one I teach how to actually enter military time into Access, which is a whole different ballgame 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.

0:01:26
We're going to use two string functions, the left and right string functions. Watch this. I want you to go watch my date serial video. This is how you can take the components of a date, like the month, day, and year, and put them together to make a date value. We're not going to use date serial in this video. We're going to use time serial, which is the same thing. You put the hours, minutes, and seconds together to make a time value, but I don't have a separate time serial video. So go watch this.

0:01:52
It's the same thing. It just works with the parts of a time instead of the parts of a day. I got to make a time serial video. I know, I know. But 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. They're on my YouTube channel.

0:02:11
Go watch those now if you don't know any of those things. And then come on back and don't take too long. You're holding up class. Go on. Go. Get out of here. All right. So 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. But basically she's got a list of names and a list of time values. These time values are stored in Excel and they're stored in numbers. 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. Because once it's a time value you can do stuff with it. You can add minutes, you can subtract hours, whatever you want to do. Treat it like a regular date time value. All right so let's go over to access. All right now let me show you what people try to do that usually doesn't work. All right so you're gonna go to external data, new, from file, Excel. All right you're gonna browse to wherever your source file is.

0:03:04
All right mine's in my drive and then spreadsheets. Where are you? Spreadsheets and there it is right there. Okay. Import, hit okay. All right this is what it looks like. First row contains the column headings, yeah. Next. Now you know me, I don't like name or time as field name. So I'm gonna change this to my name, leave it as text, and then my time. Now what a lot of people try to do is they try to change this to date with time. This is where the problem comes in. Hit next. Add a primary key, that's fine, whatever.

0:03:41
Sheet one is fine, and then finish. Okay. So now you got sheet one here, and if you open it up, 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. And in date values, a value of one is a whole day. So it actually said, okay, that's the day 1430. Now day zero in access is December 30th, 1899. Why it's a long story. So what it did was it added 1430, 1430 days onto December 30th, 1899 and that's the value that you got so we don't want to import this as a data 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 do and that's what I'm going to show you now All right, so delete this table get rid of it. Goodbye All right Let's try that again.

0:04:51
Import from file Excel. Browse, give me my sheet, okay? All right, same thing. Next we're going to change this to my name, short text, and 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, okay? All right, next. Primary key is fine, next. Import the table. Let's call it my time T. Because we're going to keep this one. All right, hit okay. Now take a look what you got. 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. All right so create query design let's bring in that my team team my team team what was what is my team team my time team all right bring everybody in okay so it looks just like that so far all right so what we're gonna do now is we're gonna first we're gonna format this guy so it's always four characters because if you got 930 right there you're missing the as the military folks say the oh oh 930 right in front of that So we're gonna make this so it's always formatted as four digits All right, I'm gonna shift f to zoom in so you can see this and we'll just call it X It doesn't matter and it's gonna be format my time as Zero zero zero zero so it's always gonna be four digits All right, and if I run that now, now you can see this is always a four digit number.

0:06:24
Okay, good. Actually, technically it's text. You know, you can tell it's lined up on the left side. Right, 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. All right, so now that we've got this as always four characters, we can break that apart into its hour and minute. All right, so come in here, and again, I'll zoom in.

0:06:42
H is going to be the left of X comma 2. Give me the left two characters. Right? And the same thing for minute, the next field, we're going to make it n though, right? Because in access, remember, m is month. So n is going to be minutes, and that's going to be the right of X comma 2. Alrighty. And now we'll take a peek. Look at that. Everyone looks perfect. Okay, 1350s down there, 0930 is up there. Now we can use time serial to build a time value, an actual date time value, right, out of these two components and that's gonna look like this. It's gonna be, we'll call it NT for new time, okay, that's gonna be time serial hours, minutes, seconds, hours, minutes, and seconds for zero. And if you do have times that have all six digits, then just break this up accordingly.

0:07:40
You're going to use left, mid, and right. Most military times that I see is usually hours and minutes. They don't usually put seconds. And now if I run that, there you go. There's an actual, valid Microsoft Access time value based on hours and minutes from the military time. See? 1430 is 2.30 p.m. Save this as new time queue.

0:08:09
There's your new time query. Now, what you gotta do is 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, now 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. All right, because this stuff here, these are calculated values and those have to get calculated before this can run.

0:08:32
So now we can make a second query. First, we got to add a field to your table to store that. How about new time? That can be an actual date time value and we can format that. If you want, you can format this as a short time. Or you can format it as medium time if you want to see it like that It's up to you. Whatever you want to see it like all right save that close it Now create another query. This will be our update query. We have to join the other two together though We have to bring in my time and our new query our new time query. It should be linked by the ID Okay, make it an update query and we're gonna update new time to what?

0:09:15
To NT. So come down here. Now where it says update to, don't just type in NT, because it's going to put it in quotes. You don't want that. You want the field NT. It's one of those times where you want to put brackets around it. There, I'll zoom in so you can see a little better. So you got to make sure you get your NT.

0:09:30
All right, we're going to update new time in the time table to the NT field, which is in the new time queue. All right, this guy that we calculated before. Since these are joined by the ID it should work just fine. Ready? All right here we go. We're gonna actually I'm gonna save this right as a time update queue in case you want to run it again. If you got to import this stuff on a regular basis you're gonna keep these things around right? Run it. All right nothing appears to happen because I got my warnings turned off. I think I explained that in the update queries video. I hate those warnings, they're annoying. Right?

0:10:04
MyTimeT, open it up and there's the value in your table now. And that is how you do that. If you wanna 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, cool i also have this nifty thing called the date time seminar which i go over pretty much everything there is to know about working with dates and times and access on a calculate workdays and reminder pop-ups and how to figure out days with holidays and uh... everything well i know about dates and times well that's what this is for, date time seminar, i'll put a link to this down below now if you've been following my tech help videos in the recent past, here 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 that I answer for people in the tech help videos.

0:11:05
And I am sad to say that ChatGPT and BARD both failed this one miserably. I literally fed in the exact question that I was going to that well that I did for this video and it no it's no it basically said to import it pick datatype date time and set the format the short time and that is completely incorrect I didn't bother trying to fix it I just all right I gave up then I went over to Bard and which is Google's version of AI and it you know kinds of weird stuff i mean it did get one thing correct though uh... i wasn't sure from the top my head what the zero was i couldn't remember days here it's it's december thirtieth eighteen ninety nine if you put a zero in the debate and i said what is the zero mike so that's a give me all kinds of weird stuff uh... and i try to get it mathematically because I know 1430 from the spreadsheet and it tried to tell me at one point in here it's trying to tell me that there's you know I said what's 1903 November 30th minus 1430 days it was giving me another day in 1403 but see no and I said that can't be right since there's 365 days in the year then it apologized and it's still gave me the wrong answer.

0:12:27
But I went over to BARD and I asked it that question and I said, what's 1430 days before November 30th, 1903? And it gave me the right answer. So at least it got its date math correct. ChatGPT, you failed miserably on both of those things. So it just goes to show you folks, you still got to take this AI stuff with a grain of salt. Okay, it's helpful, but it's not authoritative yet. But anyways that is your tech help video for today. I hope you learned something. Live long and prosper my friends and I'll see you next time. If you enjoyed this video please give me a thumbs up and post any comments you may have. I do try to read and answer all of them as soon as I can.

0:13:06
Make sure you subscribe to my channel which is completely free and click on the bell icon to select all to receive notifications when new videos are posted. Make sure you click the Show More link down below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a video, click on the link to join my mailing list. Even if you don't want to become a member, feel free to donate to my tip jar. Your patronage is greatly appreciated and will help keep these free videos coming. I got puppies to feed. How do you become a member? Click on the join button below the video. After you click the join button you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut tech help videos, one free beginner class each month, and more.

0:14:05
Gold members get access to download all of the sample databases that I build in my Tech Help videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any Tech Help questions to me, and you'll get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks, plus even higher priority for Tech Help questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for access to.

0:14:44
I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond Sponsor and have your name or company name listed on a Sponsors page that will be shown in each video as long as you're a Sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website. But don't worry, these free tech help videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It's over 4 hours long and I just updated it for 2021. You can find it on my website or on my YouTube channel. I'll include a link below that you can click on. And also, if you like Level 1, Level 2 is just $1.

0:15:34
Yep, that's all, $1 and it's free for all members of my YouTube channel at any level, even supporters. Want to have your question answered in a video just like this one? Visit my Tech Help page on my website and you can send me your question there. While you're on my site, feel free to stop by the Access Forum. Lots of good conversations happening there. Be sure to follow my blog, find me on Twitter, and of course YouTube. Once again my name is Richard Rost. Thank you for watching this tech help video brought to you by AccessLearningZone.com. I hope you enjoyed this video and that you learned something today. I'll see you again soon.