DateSerial
By Richard Rost
4 years ago
Create a Date From Year, Month, Day with DateSerial
In this Microsoft Access tutorial, I will show you how to take the individual date components (year, month, and day) and assemble them into a valid Date value that you can store in a Date/Time field.
Zack from Mesa, Arizona (a Gold Member) asks: When we first started our business a few years ago, we only tracked the year and month that our customer's joined as members. How can we convert that data to real dates so we can use them properly in Access? I want to perform calculations, like sending rewards for customers after 10 years, but I don't know how with just month and year.
Pre-Requisites
Recommended Course
Usage
- NewDate: DateSerial(MyYear, MyMonth, MyDay)
Links
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, #fasttips, DateSerial Function, get date value from month day year, month day year to date, How to use DateSerial function to get a date in MS Access
Subscribe to DateSerial
Get notifications when this page is updated
Intro In this video, you will learn how to use the DateSerial function in Microsoft Access to convert separate year and month fields into a valid date value, even if you are missing the day. I will show you how to create a calculated query field, handle records with missing data using null values, update your table to store these new date values, and ensure your database is set up for future date calculations.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to use the DateSerial function in Microsoft Access to create a valid date field from year, month, and day values.
Today's question is one I get a lot, but this one comes from Zach in Mesa, Arizona, one of my Gold members. Zach says, "When we first started our business a few years ago, we only tracked the year and month that our customers joined as members. How can we convert that data to real dates so we can use them properly in Access? I want to perform calculations like sending rewards for customers after 10 years, but I do not know how with just the month and the year."
I was chatting with Zach in email, and he was using an Excel spreadsheet. There is nothing wrong with that when you are starting a business. You can start it off in Excel, and when it gets to a certain point, move it over to Access, but all he really cared about when they joined was month and year because they do all their billing on the first of the month.
Now that you are trying to actually do date calculations in Microsoft Access using a real date field, that is a problem. We are going to have to convert what you have over to a real date value. To do that, we are going to use the DateSerial function. No, not that cereal. It is not like Cocoa Puffs.
DateSerial takes three bits of information - year, month, and day - and it assembles them together and gives you back a valid Access date value that you can store in a date field.
Now, Zach, you are missing one thing. You are missing the day. For that, we are going to have to substitute something. I would just say, since you do your billing on the first, let us just put the first of each month in there. We will take the year that you have, the month that you have, assume it is the first of that month, and then we will create a date out of that.
A prerequisite for you though - you are going to need to know how to use a calculated query field. That is where you take a query and make a value that is new based on the other fields in the query. Go watch this video first if you have never done that before.
Also, if you do not know what null values are, go watch this video. These are free videos, by the way. They are on my YouTube channel. They are on my website. Go watch these first.
We are going to use null values because you are missing some date values from some of your records. I got to look at Zach's table, so we are going to exclude those from our calculations. If you do not know what a null value is, go watch this and optionally go watch my Update Queries video because what we are going to do is take your two bits of data, your year and month, smash those together with DateSerial and make a new date value. Then we are going to update the record and put that into a new date field, and then we can delete the old stuff.
If you want, go watch my Update Queries video too so you learn how update queries work and all that stuff.
Here we go.
Here is what Zach has in his customer table. He has a couple fields down here and he actually has them named year and month, which I yelled at him for. We are supposed to have joined year as a number and joined month as a number as well. Do not use 'year' and 'month' - those are reserved words in Access, but Zach did not know. So I was easy on him.
He is missing the actual day of the month that they joined, so we have to just assume one for that. But we will get to that.
Let us put some sample data in here. I will just slide over to the right. We will put some years in here: 2020, 2010, 1995, 2013, whatever. Does not matter. That is 2008. Okay. Then we will put some months in. Okay. We have a bunch of records that are missing this information. That is okay.
Now let us go over and make a calculated query field. I will show you how to put together 2010 and May, and then we will add one for the day, and then we will make a new date field.
Let us close this. Let us go create query design. Bring in that customer table. I am just going to bring in the fields I want to work with. We are going to bring in the joined year and the joined month.
Now, we are going to set the criteria to "is not null" for both of those things. Run that. Now you can see the records that actually have data.
Back into here, let us create a new field, a calculated query field, that is going to combine those into something called 'NewDate'. We will use DateSerial. I am going to zoom in so you can see better. Shift-F2.
So NewDate is going to be DateSerial (the DateSerial function). First comes the year - joined year, then comes the month - joined month. We do not have day, so I am going to stick a '1' in there, and that will assume it is the first of that month.
There it is. Now when I run the query, look at that. There is a valid date field.
I am using ISO dates. If you have not watched that video, I will put a link to that down below as well. ISO is an internationally recognized date format that goes year, month, day. It is completely unambiguous. It does not matter what country you are in, everyone has the same format.
I have students all over the world, and you would not believe the number of emails I get and posts in my forums about people that are using my lessons - the older lessons from years ago - and they do not work because I am using US date standard, you know, month/day/year, and they have it flipped for European or whatever. I decided this year I am sticking to ISO dates from now on. It is always the same for everybody.
Now I have this NewDate. I want to stick this in my table so I do not have to keep doing this calculation all the time. Plus, new customers coming in, you are getting the date. That is right, Zach - you are actually getting, like, "okay, they joined on the 12th," and you are saving that.
Let us save this query for now. We will just call this our DateSerialQ for now.
Let us go back to our customer table. Now we need a field to stick that date in. We will just call it joined date, and that will be an actual date/time field. Save that. Right now those are all empty.
We are going to make an update query now to take this data, stick it together in DateSerial, and toss it into here.
Let us go back to our DateSerialQ. Design view. We are going to switch this to an update query. Same fields here with the same "is not null."
Now over here, we are going to add joined date, and where it says "Update To" right here, we are going to move that DateSerial formula. I am going to copy that and stick that there.
What it is going to do is update joined date to that DateSerial function. Now I can get rid of this. We do not need this anymore.
Give me joined year, joined month, is not null. For joined date, we are going to update that field to that. Save it. Now run it.
I have my warnings turned off. You will see a warning message if you do not have yours off. It will say something like "You are about to update 8 rows" or whatever. Say yes.
Now if I go and look at my customer table, slide over here. Look at that. That value is actually now stored in your table.
You only have to run that update query once, and then you can go ahead and have all your actual real dates in there. Now what if you have some that are just years? You have those with the years and the months, but maybe even your real old customers from the 80s you only got like 1987, 1985. You did not get months on those guys. So you have to assume. Let us assume January 1st of that year. We will do the same trick.
Close this. Save changes. Yep.
Now I am going to say, I have joined year is not null, joined month is null - in other words, I have the year, but I am missing the month. For joined date, you can say "is null" as well. That way you do not overwrite anybody that already has a joined date.
Since we do not know what joined month is, we are going to replace that with a one as well. Now this is basically saying, I have a year, but I do not have a month, and they do not already have a joined date, so we are going to update it to the year, January 1st.
Run it. Now let us go take a peek. Slide to the right. Look at that. 1987 January 1st.
Now you are left with all valid date fields. If this is something that you only have to do once, you do not need this query anymore. Save changes? No, you can delete that. Goodbye.
You can go in here, design view, and get rid of these fields you do not need anymore: joined year and joined month. You can go away, and I am just left with joined date, and I have all the data that I want.
Of course, back up your stuff first before you do this, folks. Back up your database before you listen to Uncle Ricky.
I always keep this slide handy. Do not be doing any deletions of fields and stuff without backing up your database first. Everybody should be backing up their computers every night. Make sure you are. Be good little children.
If you really want to learn about all this date stuff - DateSerial, date/time - you name it, all the date/time functions, I cover them in Access Expert 27 and 28, like three and a half hours with the classes. We go over DateAdd, DateDiff, DateSerial, ordinals, lots and lots of stuff on dates. I have a DateTime Seminar, which is even more in depth and covers even more with dates. If you want to learn about dates and times in Microsoft Access, I am your guy. I am like Leo Getz - whatever you need Leo gets. Remember that movie? That was a great movie.
You will find links to all that stuff and the other free videos that I mentioned earlier down below in the description underneath the video. Go click on it. Go watch it. Go enjoy it. Post your comments, your feedback. Give me a like. Give me a thumbs up. Give me whatever.
That is your Fast Tip video for today. I hope you learned something, and I will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will 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 TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will 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 TechHelp 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. 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 are a sponsor. You will 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.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What does the DateSerial function in Microsoft Access do?
A. It creates a valid date from year, month, and day values.
B. It converts text to numbers.
C. It adds days to existing dates.
D. It sorts records alphabetically.
Q2. What was Zach's original problem with storing customer join dates?
A. He only recorded the year and month, not a full date.
B. He recorded dates in text format.
C. He entered all join dates as January 1st.
D. He had duplicate date entries.
Q3. How does the video recommend handling missing day values when creating a date from year and month?
A. Use the 15th of the month.
B. Use the last day of the month.
C. Use the first of the month.
D. Leave it blank.
Q4. Before creating a calculated query field, what should you know how to do?
A. Format text fields.
B. Create and use calculated query fields.
C. Sort tables alphabetically.
D. Generate AutoNumbers.
Q5. Why should you avoid naming fields 'year' and 'month' in Access?
A. They are hard to remember.
B. They are reserved words in Access.
C. They take up too much space.
D. They cause issues with number formatting.
Q6. Why are null values important in this process?
A. They indicate missing or unknown data.
B. They act as default values.
C. They are required for formatting dates.
D. They allow text input.
Q7. When a customer joined year is given but the month is missing, what date should be assumed?
A. July 1st of that year.
B. January 1st of that year.
C. December 31st of that year.
D. The current date.
Q8. What is the primary purpose of using an update query in this video?
A. To delete old records.
B. To update the new joined date field with values created using DateSerial.
C. To import data from Excel.
D. To generate sequential IDs.
Q9. What is ISO date format as described in the video?
A. Day-Month-Year
B. Month-Day-Year
C. Year-Month-Day
D. Month-Year-Day
Q10. Why is backing up your database emphasized before making changes like deleting fields?
A. To speed up the database.
B. To share it with others.
C. To prevent accidental loss of data.
D. To reduce file size.
Q11. After updating your database with the new date field, what should you do with the old year and month fields?
A. Leave them in the table for reference.
B. Rename them to avoid confusion.
C. Delete them once you confirm the data is correct.
D. Convert them to text fields.
Q12. What is one benefit of moving from Excel to Access for customer date data?
A. Access cannot store dates.
B. Access allows for date calculations and queries.
C. Access requires no setup.
D. Excel has better date functions than Access.
Answers: 1-A; 2-A; 3-C; 4-B; 5-B; 6-A; 7-B; 8-B; 9-C; 10-C; 11-C; 12-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 video from Access Learning Zone covers how to use the DateSerial function in Microsoft Access to convert individual year and month values into a valid date field.
This is a common scenario for many businesses. In this particular case, Zach reached out and shared that his business originally only tracked the year and the month when customers joined, but not the exact day. Their data, which started in Excel, only had year and month details since billing always occurred at the start of each month. Now, with the move to Microsoft Access, they want to treat those values as actual dates so they can perform useful calculations, like determining when customers have reached a 10-year anniversary for loyalty rewards.
The challenge with this kind of data is that Access requires a full date for proper date calculations. Year and month alone aren't enough. This is where the DateSerial function becomes extremely useful. DateSerial combines three values: year, month, and day, and assembles them into a valid date value that Access recognizes. Since in Zach's case the day was not recorded, we can substitute '1' for the day, assuming all customers joined on the first day of the month.
Before proceeding, it's important that you're comfortable working with calculated query fields in Access. These allow you to create a new value in a query, using existing fields from your tables. If you are not familiar with this, I have free tutorials available on my website and YouTube channel that explain calculated query fields and working with null values. Since some records might not have year or month information, handling null values is relevant to this task.
The process starts by examining the customer table, which in Zach's situation contained 'year' and 'month' fields. It's important to mention that using 'year' and 'month' as field names in Access can cause problems, as they are reserved words. Ideally, you should use something like 'joined year' and 'joined month' as numeric fields.
With your sample data ready, including some records that are missing year or month values, you can now create a calculated field in your query. In the query design, bring in only the 'joined year' and 'joined month' fields. Apply criteria to exclude records where either field is null, as these cannot be turned into valid dates. For the calculated field, use the DateSerial function, passing in the year, month, and '1' for the day. This creates a new field, which you can call 'NewDate,' that represents the customer's join date as the first of the respective month and year.
The next step is to store these generated dates in the actual customer table. Add a new field, such as 'joined date,' as a date/time type. Then, switch your query to an update query, and set it up to transfer the calculated DateSerial value into the new 'joined date' field. Once you run the update, the date values are saved permanently in your table.
If you have records where only the year is available (no month information), you can modify the update query accordingly. Set it so that any record with a non-null year and a null month gets a 'joined date' of January 1st for that year. By handling these cases, you will ensure every customer has a valid join date.
Once the actual dates are stored, you may no longer need the old 'joined year' and 'joined month' fields. Before deleting anything, always make sure to back up your database so you do not lose important data. It's easy to overlook a step and delete something by accident, so better safe than sorry.
For those interested in learning even more about working with dates in Access, my Access Expert 27 and 28 courses cover DateSerial and many other date-related functions in great detail. There's also a complete DateTime Seminar for more advanced needs. Links to these lessons and the free video tutorials I mentioned can be found on my website.
If you would like to support my work and get even more resources, you can sign up for membership, which comes in several levels with increasing benefits. Silver members get access to all my extended cut TechHelp videos and free beginner classes. Gold members receive downloadable sample databases, access to my code vault, higher priority for submitting questions, and advanced courses. Platinum members receive all previous perks plus even more access to full courses on a range of topics, including Access, Word, Excel, and Visual Basic.
No matter your membership level, all of my standard TechHelp videos will remain free as always.
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 Using DateSerial to combine year, month, and day into a date
Handling missing day values by defaulting to the first
Creating a calculated query field for date conversion
Setting query criteria to exclude null values
Creating a new date field in a table for joined dates
Building and running an update query to populate the date field
Handling records with only year and missing month data
Defaulting missing month values to January in DateSerial
Removing old year and month fields after migration
Backing up your database before making structural changes
Storing ISO formatted dates for consistency across regions |
