Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for DateTime Seminar Lessons

Age	Subject	From
2 years	Recurrent appointment	Ewa Gustafsson
2 years	Video File	James Gray

Subscribe to DateTime Seminar Lessons
Get notifications when this page is updated

Intro

In this lesson you will learn a variety of advanced techniques for working with dates and times in Microsoft Access. We will cover topics such as calculating the number of work days between two dates (excluding weekends and holidays), time-stamping edited records, creating pop-up reminder notifications, counting the number of specific weekdays in a date range, calculating dates for holidays (including complex ones like Easter), and setting up recurring appointments. Most of these lessons involve some programming with VBA, but each step is explained clearly so you can follow along even if you're new to coding.

Transcript

Welcome to the Advanced Date Time Seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's seminar is going to cover a bunch of advanced techniques that have to do with dealing with dates and times. These are a lot of questions that I got asked during my normal Access Expert classes, Levels 27 and 28, where I covered the date time functions, but all of these techniques require at least a little bit of programming. In my Access Expert series, I am trying to avoid using VBA programming, so I decided to put all this together in a seminar for you.

We are going to cover popular topics such as calculating the number of work days between two dates. For example, give me January 5th and February 5th. I want to know how many Monday through Friday days there are in there, and I will show you how to exclude holidays by making a holiday table.

We will learn how to date time stamp edited records. It is very easy to figure out when a record is created, but what if you want to know when that record was edited? I will show you how to time stamp it as soon as it is edited.

Lots of people want to know how to generate pop-up reminder notifications in Access. So you can be working on something and then all of a sudden your database will go, oh, you have a reminder, you have to make a phone call in 5 minutes. Those are pop-up reminders.

I will show you how to figure out how many Mondays are in January, or how many Tuesdays are in February, or how many Thursdays are in the third quarter. We will do all kinds of different calculations like that, that involve counting the number of a particular type of day between two date ranges.

We will learn how to calculate holiday dates. Give me a year, and I will tell you what day the easy ones fall on, like Christmas or New Year's Day, those are simple. The more complicated ones, such as Martin Luther King Day or Thanksgiving Day, and we will even see how to calculate the really crazy ones. Like Easter Sunday has a very complicated formula. We will take a look at that later.

And finally, a popular request. We will see how to create recurring appointments. We will make a list of appointments or reminders in our database. We will set a frequency, such as I want to do this every three days or every two weeks. When we mark it done, the database will automatically advance it that far into the future.

So lots of different cool techniques today, and again, all of these involve a little bit of programming, but that's okay. Don't worry if you've never done any programming before. I'll show you everything you need to know step by step.

Again, I mentioned the prerequisites earlier: Access Expert 27 and 28. I strongly recommend before taking this class, you have taken my complete beginner series in Access, and all of my Expert classes Levels 1 through 28.

In 27, I cover the basic date time functions, date time now, teach you about date mathematics, and we'll build a simple accounts receivable with date aging in it, so I can say show me all the orders that are 30 days past due, 60 days past due, and so on.

Then in Expert 28, we go through all of the date time functions in detail, the more advanced ones: day, month, year, hour, minute, second, week day, week day name, month name, date add for adding dates, date diff for calculating the difference between two dates, date serial, date part, date value, time value, and convert date.

A helpful but not required prerequisite is my Access SQL Seminar Part 1. When we build our reminder popup form, I'm going to use a little tiny bit of SQL in there to dynamically recreate the form and show some data on there in different ways. It will be helpful if you've taken the SQL Seminar first, but not required. Again, I'll show you everything you need to know today. But if you want to learn more about SQL, I recommend taking that first before this one.

This seminar was recorded with Access 2013. I'm pretty sure everything that I covered will work in 2010 and 2007 as well. Most of the VBA, if not all of the VBA, will work with 2003 and earlier, but the lessons that talk about embedded macros, for example, the timestamp, will not work. You'll have to use the VBA code lesson for that.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you're in now.

When you finish all of the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you're watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video.

Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you're learning right now to other projects until you've mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what's wrong in the student forum and I'll do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.

Now I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You'll find there's a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we're going to cover in today's class.

One of the most popular questions I get with Microsoft Access when it comes to date time functions is how do I replicate the network days function from Microsoft Excel? Network days in Excel allows you to enter two dates and it will return the number of work days Monday through Friday plus an optional list of holiday dates, and it will tell you how many work days are in that range. Access does not have that functionality.

So in lessons one, two, and three, I'm going to show you how to build something like that in Microsoft Access. In lesson two, we're continuing on with the work days function. Lesson three concludes the three lessons on the network days function.

When this lesson is finished, we'll not only have a function that mimics the network days function of Excel where you can count the number of work days between two dates, but we'll also build our own is work day function where we can check any single specific date to see if that date is a work day, whether it's a Monday through Friday or it's on our holiday list of excluded dates and so on.

In lesson four, we're going to learn how to time stamp records when they're edited. So when someone makes a change to a record, either in the table or the form, we'll set a last updated field equal to the current date and time.

In the next couple of lessons, we're going to learn how to make event pop-up notifications. We'll make a list of reminders, what the notification date and time is, and then we'll make a form that will pop up automatically and say, hey, you've got a reminder four or five minutes from now.

In lesson five, we'll begin by learning how to program the on-timer event. In lesson six, we're continuing with the reminder pop-up notifications. This is part two. We'll build an actual reminder pop-up form.

In lesson seven, we're continuing with our reminder pop-up notifications. This is part three. We'll work on the remind me button, so you can say remind me in five minutes, in ten minutes, and then move the reminder buttons to move it to tomorrow, next week, and so on.

In lesson eight, I'm going to show you how to calculate how many X days in a particular interval. Now by X day, I mean a specific day of the week, for example, how many Wednesdays are in this month, how many Thursdays are in next quarter. This is an example that I did not cover in Access Expert 28 because it involves some programming, so I saved it for the seminar.

In lesson nine, we're going to learn how to calculate holiday dates. We'll start off with some easy ones, like U.S. Independence Day and Christmas Day. Then we'll get into some more complex ones, like President's Day, the third Monday of February, or Labor Day, the first Monday of September. Then we'll get into some really crazy stuff like how to calculate Easter Sunday.

In lesson ten, we're going to take our reminders form that we created in earlier lessons. We're going to set it so that each of our reminders can be recurring. So I can say, when I click the Done button, move this ahead three days, or two weeks, or four months, or whatever. This is good for reminders to pay bills, or to check your YouTube channel, or any kind of regular recurring reminders you have. We'll also make buttons so we can advance through, show me this week, last week, the week after that, and so on.

In lesson eleven, we are continuing with our recurring appointments form.

In lesson twelve, I'll show you just a real short function that I wrote a while back to display ordinal dates, that's first, fifth, seventh, thirty-first, and so on.

Thank you.

Quiz

Q1. What is one of the main advanced topics this seminar will cover regarding dates in Access?
A. Creating pivot tables based on dates
B. Calculating the number of work days between two dates, excluding holidays
C. Importing calendar events from Outlook
D. Exporting reports in date format to PDF

Q2. What does the seminar suggest as a prerequisite before attending this course?
A. Access Developer series
B. Access Beginner and Expert Levels 1 through 28
C. Experience with Microsoft Excel only
D. Working knowledge of SQL Server Management Studio

Q3. What technique will be demonstrated to record when a record is updated in Access?
A. Data validation rules
B. Applying a date/time stamp whenever a record is edited
C. Setting default field values
D. Using lookup tables for time zones

Q4. Which of the following is NOT mentioned as a seminar topic?
A. Calculating number of Mondays in a month
B. Generating pop-up reminder notifications
C. Generating barcode labels from date fields
D. Creating recurring appointments in Access

Q5. When learning to calculate holiday dates, what type of dates does the seminar mention are "easy"?
A. Dates that change every year without a fixed rule
B. Fixed holidays like Christmas or New Year's Day
C. Holidays based on moon phases
D. Holidays celebrated only every four years

Q6. What function from Excel does the seminar aim to replicate in Access?
A. VLOOKUP
B. NETWORKDAYS
C. CONCATENATE
D. SUMIF

Q7. According to the seminar, what is recommended if a student gets stuck or does not understand something?
A. Quit Access and restart the lesson another day
B. Watch the video again or ask for help in the student forum
C. Hire a programmer to complete the exercise
D. Skip the difficult section

Q8. What is suggested as the best way to learn Access based on the instructor's own experience?
A. Only read through manuals
B. Tear apart sample databases and rebuild them
C. Attend classroom-based training sessions exclusively
D. Focus on memorizing VBA keywords

Q9. What advanced date calculation will be taught that was specifically not included in Expert 28 due to requiring programming?
A. Calculating the fiscal quarter start date
B. Calculating how many specific weekdays fall within a date range
C. Importing bank holidays from online sources
D. Sorting records by time zone

Q10. What tool or code type will likely NOT work in Access 2003 and earlier, according to the seminar?
A. SQL queries for simple selects
B. Embedded macros for timestamping
C. Standard VBA functions
D. Table relationships

Q11. What is the recommended approach for following the class material to maximize learning?
A. Try to apply techniques to a real project immediately
B. Watch each lesson through, then replay and follow along with steps in the sample database
C. Focus only on the lesson you are interested in
D. Skip exercises that involve programming

Q12. What resource is available to students if they want to learn about other Access topics outside of date and time?
A. Sample barcode templates
B. Seminars covering topics like web-based databases, scheduling, SQL, and more on the instructor's website
C. Access-only chat rooms
D. Phone-based support lines

Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 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 the Access Learning Zone is the Advanced Date Time Seminar, where I tackle a collection of advanced techniques for working with dates and times in Microsoft Access. These topics come from questions that often come up in my Access Expert classes, especially when covering date and time functions, but each one involves at least a small amount of programming. In my regular Expert series I keep VBA to a minimum, so I've put together this seminar specifically to address those needs that go beyond the basics.

One key topic I discuss is how to calculate the number of work days between two dates. For instance, if you want to find out how many Monday-through-Friday days are between January 5th and February 5th, I'll show you how to determine that and how to set up a holiday table so you can exclude specific dates as needed.

We'll also cover how to place a date and time stamp on a record the moment it is edited. While tracking the creation date of a record is simple, many people want to know when a record was last modified. I'll walk you through how to add that functionality as soon as someone updates a record.

If you've wondered about automatic pop-up reminders in Access, you're in luck. I'll demonstrate how to create notifications that remind you of upcoming tasks, like a phone call you need to make in five minutes. These pop-up reminders are a popular request.

I also explain how to calculate how many specific weekdays are in a given period. For example, if you want to know how many Mondays are in January or how many Thursdays fall in the third quarter, I will guide you through building those calculations.

Another part of the seminar focuses on calculating holiday dates. For straightforward dates such as Christmas or New Year's Day, the process is quite simple. For more complex holidays like Martin Luther King Jr. Day or Thanksgiving, which do not fall on the same date each year, I'll show you how to compute their exact day. We'll even cover the method for determining dates like Easter, which uses a far more involved formula.

Recurring appointments are a frequent request as well. We will set up a database to track reminders or appointments, specify the recurrence frequency (like every three days or every two weeks), and configure the database to automatically update future reminders once you mark the current one as completed.

All these lessons will involve some programming, but there's no need to be worried if this is new to you. I'll guide you step-by-step through every concept and process.

As for prerequisites, you should have completed Access Expert Levels 27 and 28 before attending this seminar. Both are important, as Level 27 introduces the basics of date and time functions, date mathematics, and building applications like simple accounts receivable with date aging. Level 28 dives into the full range of built-in date and time functions, covering things like day, month, year, hour, minute, second, weekday, weekday name, month name, date add, date diff, date serial, date part, date value, time value, and converting dates.

Though not required, completing my Access SQL Seminar Part 1 will be helpful. There is a portion of the seminar where we use a bit of SQL to build the reminder pop-up form. You don't have to know SQL beforehand, but if you want a deeper understanding, you might want to take that course first.

This seminar was recorded using Access 2013, but nearly everything we cover will also work in Access 2010 and 2007. Most of the VBA content will function with Access 2003 and earlier as well, except for lessons involving embedded macros such as the timestamp feature. In those cases, you'll use the VBA version of the lesson instead.

My courses are structured by skill level: beginner, expert, advanced, and developer. By the time you reach the expert level, you should feel comfortable with all beginner topics. The advanced classes build on these concepts with event programming and macros, and the developer classes focus on Visual Basic for Applications. Each group is divided into levels, so you can steadily progress.

In addition to the main Access courses, I also offer seminars on specialized topics, like building web-based databases, making calendar-style forms and reports, database security, integrating images and attachments, tracking work orders and service businesses, managing accounts payable, learning SQL, creating loan amortization schedules, and more. Details on these seminars are available on my website.

If anything covered in today's seminar raises questions, I invite you to post them in the student forums, especially if you are watching through the online theater on my website. Each lesson has its own forum area, where you can see what other students have asked, read my responses, and participate in discussions. If you're not watching on the Access Learning Zone website, you can still access the forums directly via the site.

To get the most out of this course, I recommend you first watch each lesson without trying to follow along. After that, go back and replay the lesson while building the same sample database I demonstrate. Once you have a solid understanding using the practice database, then you can start applying techniques to your own projects.

If you get stuck or something is unclear, revisit the lesson or describe your issue in the student forum so I can assist you. Keep an open mind. While Access can seem overwhelming initially, with practice it becomes manageable and even enjoyable.

I highly encourage everyone to build the database along with me during the class, but if you want a copy of my finished sample database, you can download it with the others from my website. Sometimes taking apart a working sample is the best way to learn. That's how I learned much of Access myself, studying the Northwind Traders database that comes with the software. You'll find a sample database for each of my courses on my site.

Now, let's briefly summarize what topics are covered in the seminar.

One of the most frequent requests I get is how to replicate Excel's network days function in Access. This Excel function counts work days between two dates (Monday through Friday), optionally excluding holidays. Access does not have this built in, so in lessons one, two, and three, I show you how to build a similar setup. By the end of these lessons, you'll not only have your own function to count work days, but you'll also have a way to check any specific date to see if it qualifies as a work day depending on weekdays and holidays.

Lesson four focuses on adding time stamps to records when they are edited, capturing the date and time each time a record is changed.

The next set of lessons tackles event-based pop-up notifications. You'll learn to make a reminder list, define notification times, and set up an Access form that pops up and reminds you of pending tasks. In lesson five, you'll be introduced to the timer event. Lessons six and seven build on this: you'll create an interactive form that lets you snooze reminders or move them to a different day.

Lesson eight teaches you how to calculate how many of a particular day (like Wednesdays or Fridays) occur within a given date interval. This is not covered in my Access Expert 28 class because it needs some programming, so it's presented here.

Lesson nine addresses calculating all kinds of holiday dates, from easy ones like Independence Day and Christmas, to harder ones like Presidents Day (the third Monday in February) and Labor Day (the first Monday in September), to even more complex movable feasts like Easter Sunday.

In lesson ten, I build on the reminders tool we set up earlier, adding the ability to set up recurring reminders or appointments. Completing a reminder automatically reschedules it for its next recurrence, whether it's every few days, weeks, or months. We'll also add buttons to browse reminders by week.

Lesson eleven continues improving the recurring appointments setup.

Finally, lesson twelve demonstrates a short function to display ordinal dates, so you can show date labels like first, fifth, seventh, or thirty-first.

You can watch a full video tutorial with step-by-step instructions for everything mentioned here on my website at the link below. Live long and prosper, my friends.

Topic List

Calculating work days between two dates in Access
Excluding holidays using a holiday table
Date and time stamping edited records
Creating pop-up reminder notifications
Programming the on-timer event for reminders
Building a reminder pop-up form
Adding "remind me" and reschedule buttons
Counting specific weekdays in a date range
Calculating holiday dates including complex holidays
Calculating dates for holidays like Easter using formulas
Creating and managing recurring appointments
Advancing appointments by custom intervals
Displaying ordinal dates in Access