|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
DateDiff By Richard Rost 
6 years ago
Learn to use the DateDiff Function in Access Calculate the number of days, weeks, months, etc. between two dates with the DateDiff Function MembersThere is no Extended Cut for this video. LinksDateDiff Glossary Reference Format
IntroIn this video, I will show you how to calculate the difference between dates in Microsoft Access using both simple date subtraction and the DateDiff function. We will talk about how to display the number of days or weeks between today's date and a date stored in your table, create calculated query fields to update this information automatically, and discuss different time unit options with DateDiff. You'll also learn about the importance of using dynamic dates and how to avoid common mistakes when setting up your queries.TranscriptWelcome to another TechHelp with Richard Rost brought to you by AccessLearningZone.com.In today's class, I'm going to teach you about the DateDif function, which is a function that we can use to determine the difference between two dates in your database. Today's question comes from Thomas. He says, "Dear Richard, how can I have a field in my database that will represent the current date always, not the current date of the record, but the actual current date? Today's date. I'm trying to show items that are open for a certain amount of days and work in the quality field and I'm creating a non-conformance database with queries for how long a non-conformance is open." Basically, you have records in your table and you want to be able to see the difference in days between some date in the table and today's date. You might want to know, for example, when was the last date that the employee showed up for work, and if it was a week ago, you'd want to see a seven there. Or if it was two days ago, you'd want to see a two. We're calculating the difference between today's date and some other date in the database. Let's say we have a table here. We've got CustomerID or AutoNumber, the customer name (Short Text), and CustomerSince (Date/Time), representing how long they've been a customer. Let's save this as MyCustomerT. Very key to define, yes. I cover all this in my beginner class. Make sure you watch that if you don't know what I'm doing here. Let's put some simple data in here. Customer is Joe. Joe has been a customer, and today's date is... what is today's date? Today's date is March 31, 2020. So let's put in here 3/1/20. I'm going to 2/9/20. Can't type today. There we go. Sue, let's from 2/1/15 and Bill from 1/1/99 just to see. Now, since this is going to be a dynamic value, I'm not going to put a field in the table for it. I'm going to build a query and the query will do the calculation using a calculated field. I cover calculated fields in another video. I'll put links to all the things I mention in the description below the video. A calculated field is something you do in a query to have Access generate information on the fly. This is the data that's stored in the actual database in the table. Today's date is going to change, so we're not going to store that in the table. Save changes. Yes. Let's go over to queries, create query design. Let's add MyCustomerT and go ahead and bring in all the fields. I'll bring in the star. If I run this query right now, you can see this is what we've got. That's basically the fields from our table. Now, right over here, we're going to put in a calculated field that's going to determine the difference in the number of days between today's date and the CustomerSince field. In Access, whole days are given a value of one. So one day equals 1.0. Half a day, so 12 hours, is 0.5. An hour is 1/24 of a day. If you want to calculate the number of days between today's date and the date in the table, you simply do a subtraction. The higher date goes first, and we can get the current date using the Date() function. I'm going to make my own calculated field here called NumDays:. I have a separate video on calculated query fields. NumDays is going to be the Date function (that's Date with open and close parentheses), minus [CustomerSince], the CustomerSince field. Access puts square brackets around it since it recognizes it as a field. Let's save this as MyNumDaysQ query and run it. Now you can see there's the number of days difference between CustomerSince and today's date. As today's date changes, this number will change automatically. You don't have to go in and redo anything. If you want to see which customers have the oldest invoices, which employees haven't been into work, or who your oldest customers are, that will do it. There is another way you can calculate the difference between two days, and that's using something called the DateDif function. Let's go back into design view. Right over here, let's call this DD in my field. We're going to say DateDiff(. The DateDiff function takes three bits of information. The first is what kind of time unit you want to take the difference in. You can do the difference in days, weeks, months, or years. The DateDiff function is versatile because you can do the difference in different ways. I want days, so inside quotes just put "d", then comma. Then you want the two dates, Date1, Date2. So again, Date(), [CustomerSince], like that. Save it and then run it. Oh, I got them backwards; it's showing up as negatives. Just flip the dates. The bigger date has to be at the end. I do that all the time. I can never remember which one is supposed to be first. [CustomerSince], Date(), just like that. Or you can multiply it by negative one. Same results. The cool thing about DateDiff is you can use it if you want the number of weeks, for example. Let's make this a little bit bigger here. Let's go DDW for DateDiff Weeks. Just change this to "ww". "w" is weekday; "ww" is full weeks. Now if I run it, you can see that's the number of full weeks. And it seems about right, 30 days, 4 weeks, roughly. That's whole weeks; you don't get a fractional component. Here are all the codes you can use. There are year, quarter, month, day of year, day, weekday (which doesn't include weekends like Saturdays and Sundays), weeks, and then hours, minutes, and seconds. DateDiff does also work with times. If you really want to learn a lot about Access and how it handles dates and times, my Access Expert 27 and 28 classes cover all kinds of stuff on working with dates and times in Access. I do things like build an aged accounts receivable report and go through all kinds of different functions. I'll put a list of what's covered in those classes in the description below. There is a link that you can use to find those on my website. In fact, one of the things that people ask me the most about in Access is how to calculate someone's exact age. Maybe if you need to know if they're 21, for example, that's a little more complicated. It uses a different function called DateSerial. It's very useful. If you want to see it enough, let me know, drop a comment in the description below, and I may do a separate YouTube video on this, but I cover that in Access Expert 28 as well - how to calculate someone's exact age to the day, actually to the second. If you want to learn more about the Date function, I cover it in Access Beginner Level 5. Take a look in the description below. I've got links to other YouTube videos I've posted including Calculated Query Fields, which is something that a lot of beginner users need to learn how to do, and more on Access Date and Time calculations. There's also a link on how to do this same thing in Excel. There's a DateDif function in Excel, which is undocumented. A lot of people don't know about it, and I teach how to do that as well. Hope you learned something. Make sure you subscribe and get notifications for new videos. If you want to see your question answered right here on my YouTube channel, just email it to me. You can find all the contact information in the description below. Let me give it to you right here on the screen. There it is. There's a link to my TechHelp page. You can drop a question there. Want to email me directly? There you go: [email protected]. I get lots and lots of email, so sometimes it's better to use the website, but if you do want to just drop me an email, you can go right there. There's all my stuff: my blog, Facebook, Twitter, YouTube, all that. And one little bit of promotion, there's a link to my free Access Level 1 course, three hours long. If you want to learn from the best, from the basics, you can start right there. If you like that, Level 2 is just $1. Thanks for learning with AccessLearningZone.com and we'll see you again soon. QuizQ1. What is the primary purpose of the DateDiff function in Access?A. To calculate the number of days between two dates B. To sort dates in ascending order C. To insert the current date in a table D. To validate date inputs Q2. Why should you use a calculated field in a query to find the difference between today and another date, instead of storing that value in the table? A. Because today's date changes and recalculating each time gives up-to-date results B. Because you cannot do calculations in tables C. Because Access does not allow date fields in tables D. Because queries cannot perform calculations Q3. Which function in Access provides the current date? A. Now() B. Date() C. Time() D. Today() Q4. When performing date calculations in Access, what does Access use to represent one whole day? A. 0.5 B. 2 C. 1.0 D. 24 Q5. In the DateDiff function, what does the first argument specify? A. The formatting of the result B. The field to update C. The time unit for the difference (e.g., days, weeks, months) D. The order of sorting Q6. If you want to calculate the difference in full weeks between two dates using DateDiff, which code should you use? A. "d" B. "w" C. "ww" D. "m" Q7. What happens if you reverse the order of the dates in the DateDiff function (earlier date first)? A. The result is always zero B. The result appears as a negative number C. The result remains unchanged D. Access returns an error Q8. Which of the following is NOT a valid interval code for DateDiff? A. "y" (Year) B. "q" (Quarter) C. "hh" (Hours) D. "mm" (Minutes) Q9. Which other Microsoft product also contains a version of the DateDif function, as mentioned in the video? A. PowerPoint B. Word C. Excel D. OneNote Q10. Why is it not necessary to create a field in your table for a dynamic value like the number of days between two dates? A. Because it can be generated on the fly using calculated fields in queries B. Because only text fields can be added to tables C. Because calculated fields can only be stored in forms D. Because tables are read-only Q11. According to the video, if you want more detailed examples and advanced handling of dates in Access, which courses should you consider? A. Access Beginner 5 B. Access Expert 27 and 28 C. Access Level 1 D. Access Advanced Forms Q12. What is the function called that can help calculate someone's exact age to the day or second in Access? A. DateAdd B. DateSerial C. Now D. TimeValue Answers: 1-A; 2-A; 3-B; 4-C; 5-C; 6-C; 7-B; 8-D; 9-C; 10-A; 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. SummaryToday's video from Access Learning Zone focuses on the DateDiff function in Microsoft Access, a powerful tool for calculating the difference between two dates in your database.The inspiration for this lesson came from a student who asked how to display the current date in a field, not just the date stored with a particular record. Specifically, he was interested in measuring how many days an item has been open in a non-conformance database. This type of calculation is common if you need to track how long it has been since a particular event, such as the last time an employee checked in, or how long a customer has been with your business. Let's start by considering a simple customer table. For this example, imagine a table with fields for CustomerID (as an AutoNumber), CustomerName (as Short Text), and CustomerSince (as a Date/Time field). This setup helps us track how long each person has been a customer. Suppose we have data for customers like Joe, Sue, and Bill, each with their respective CustomerSince dates. Because the calculation involves today's date, which changes every day, we will not store the difference between dates as a field in the table. Instead, we use a query with a calculated field so that Access generates the result dynamically each time you run the query. Calculated fields in queries are an ideal way to handle values that depend on the current date or other variables that change over time. To create this query, you bring all your fields into the query design and then add a calculated field to find the difference in days between today's date and the CustomerSince date. In Access, a single day is represented as 1.0, so to get the number of days, you simply subtract the CustomerSince value from the current date, which you get using the Date function. You might call this calculated field NumDays, and it simply contains the formula that subtracts CustomerSince from today's date. When you run the query, you will see the difference in days for each customer. Since the Date function always returns the current date, these results automatically update each day. If you want greater flexibility, you can use the DateDiff function instead. DateDiff not only allows you to calculate the difference in days but also in weeks, months, years, and more. The function expects three arguments: the time unit you want (such as "d" for days or "ww" for weeks), the starting date, and the ending date. For example, you can set up a field in your query to calculate the total number of days or whole weeks between CustomerSince and today's date. Just be sure to put the earlier date first; otherwise, you may end up with negative numbers. Access provides codes to use with DateDiff for other units like years ("yyyy"), quarters ("q"), months ("m"), day of year ("y"), day ("d"), weekday ("w"), weeks ("ww"), as well as hours, minutes, and seconds. DateDiff can also compare times if you are dealing with times of day. If you are interested in mastering dates and times in Access, my Access Expert Level 27 and 28 courses go into detail about these topics, including building more advanced reports like aged accounts receivable and age calculations. Calculating someone's exact age, for example, takes a bit more effort and typically uses the DateSerial function, which I explain in Access Expert 28. For those just getting started, I recommend looking at my beginner-level courses and videos on calculated query fields and date/time calculations. If you're an Excel user, you might be surprised to learn that Excel also has a DateDif function, though it is undocumented; I have a tutorial covering that as well. If you have questions you want answered, you can email them to me or use the TechHelp page on my website. For those who are new to Access, there is also a free three-hour beginner course available, with the option to continue on to Level 2 for just $1. 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 ListUsing the Date function to get today's date in AccessCreating a sample table with date fields in Access Entering sample date data for customers Building a query to calculate days between dates Creating a calculated field in a query for date difference Using subtraction to find the difference in days between dates Using the DateDiff function to calculate date differences Specifying time units (days, weeks) in the DateDiff function Correcting order of arguments in DateDiff to avoid negatives Calculating number of weeks with DateDiff using "ww" Listing common DateDiff interval codes (year, quarter, month, day, week, etc.) Demonstrating DateDiff functionality with time values |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access tips datediff PermaLink DateDiff in Microsoft Access |
