|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Date Math By Richard Rost 
4 years ago
Date/Time Calculations: Day, Wk, Hr, Min, Sec In today's video, I'm going to show you how to perform date/time calculations in Microsoft Access using whole days or fractions thereof. You can calculate values in days, weeks, hours, minutes, and seconds using just simple math with no complex functions to learn. Links
Recommended Course
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, date calculations, Calculating Dates, Date Calculation in Microsoft Access, How do you calculate dates in Access, How do I calculate the number of days between two dates in Access, How do you add 30 days to a date in Access, Add to or subtract from date and time values, calculate due date in access, Add to or subtract from date and time values
IntroIn this video, I will show you how to perform date math in Microsoft Access, including working with days, weeks, hours, minutes, and seconds using date values in tables, queries, and reports. We'll cover how to add and subtract dates, calculate due dates, find the difference between two dates, and use date-based query criteria. You'll also learn about using fractions of a day for time calculations and get an introduction to the DateAdd and DateDiff functions for working with months and larger date intervals.TranscriptIn today's video, I'm going to show you how to perform date/time calculations with whole days or fractions of a day like hours and minutes in Microsoft Access.Before you watch this video, make sure you understand query criteria and calculated query fields. If not, go watch these videos. You'll find links down below. In Microsoft Access, date values are numbers where a value of 1 equals 1 day. Because of that, we can do some pretty cool stuff. For example, if you have a field called "my date," which could be any date value, if you add 1 to it, that's tomorrow. If you subtract 1 from it, that's yesterday. If you want to add a week, add 7; if you want to add 30 days, add 30, and so on. Here, for example, I made a query where I brought in a table, which has just an ID and a date field. Then, I made a calculated query field. It says "x: my date + 30." This will add 30 days to that date. Now, when I run the query, there you go. There is a list of dates that are all 30 days after "my date." If you have invoices, for example, you can make your due date 30 days in the future by using a calculated field right in the report. There is my text box right there, and you can see it is "=OrderDate+30." You just put that right in the properties in the control source. You can use this technique to calculate the difference between two dates in days. For example, "end date minus start date" will give you the count in days. Here I have another table where I've got a start date and an end date. Right here, I put "DayCount: EndDate - StartDate." Make sure you have the larger date first. Larger dates are dates in the future. When I run this query, you can see there is my day count between each of these sets of dates. You can use date values as criteria, either with static dates (and remember, you have to put dates inside of pound symbols like this). This says all dates less than February 1, 2022. Or this one says greater than or equal to 2/1/22. Or you can use the Date function, which puts the current date in there - the current system clock date, whatever your computer is set to. So this is "less than today's date," "less than seven days from now," which is within a week, or "less than 30 days from now." In this query, for example, I'm saying, "Show me all the records with dates less than 30 days from now." You can use this with orders, for example, to say, "Show me all of the orders that are due within the next 30 days." If I run this, there you go. Again, today is 2/19/2022. Ignore the last record. Remember, that's a blank new record that does not really exist yet. I can flip that around and say "greater than today's date," and that will show me all of the orders that are not due yet. These are all future dates. Again, ignore that last one. Knowing that the value of 1 is equal to a day, you can use fractions of a day to calculate hours, minutes, and seconds. For example, if you add 1/24 to a date value, it will give you 1 hour in the future. 1/(24*60) is 1 minute. Let Access do the math. You could figure that calculation out yourself, but just let Access do it. Here in this query, for example, I'm taking a time value, and this field will add 1 hour to it (plus 1/24). This field will add 1 minute to it. In Access, "m" is for month, and "n" is for minutes, so I used "n" there. When I run this query, there you go. This column is my time plus 1 hour, and this is my time plus 1 minute. You could do seconds by dividing by 60 again. You can use the techniques I just showed you to calculate days, weeks, hours, minutes, and seconds. But if you want to calculate whole months, that's a little bit trickier. There are some functions called DateDiff and DateAdd, which you can use to calculate months, quarters, years, and more. If you need to calculate calendar months, for example, between two dates, these functions will give you that. It's a little more complicated than just individual days, because some months have 30 days, some have 31, some have 28, and so on. I have more free videos that explain these functions. Again, you will find links down below. There you go. There is everything you need to know to do some basic date math in your Microsoft Access databases. I hope you enjoyed this, and I hope you learned something. We'll see you next time. QuizQ1. In Microsoft Access, what does a date value of 1 represent?A. One week B. One day C. One hour D. One month Q2. If you want to add 7 days to a date in Access, what calculation should you use? A. Date + 1 B. Date + 24 C. Date + 7 D. Date * 7 Q3. What is the purpose of the calculated query field "x: my date + 30"? A. To subtract 30 days from a date B. To divide the date value by 30 C. To set the date to the 30th of the month D. To add 30 days to a date Q4. When calculating the difference between two dates, which formula gives you the number of days? A. StartDate + EndDate B. EndDate - StartDate C. StartDate - EndDate D. EndDate / StartDate Q5. In Microsoft Access date criteria, how should dates be formatted? A. Use double quotes B. Use single quotes C. Use parentheses D. Use pound symbols (#) Q6. Which function returns the current system date in Access? A. Now() B. Time() C. Date() D. CurrentDate() Q7. What does adding 1/24 to a date value accomplish in Microsoft Access? A. Adds 24 hours B. Adds 1 hour C. Adds 1 minute D. Adds 1 second Q8. Which letter represents minutes in Access date formatting? A. m B. h C. s D. n Q9. If you want to calculate the difference in months between two dates in Access, which function should you use? A. DateDiff B. Now C. Today D. DateValue Q10. Why are calculations involving calendar months more complex than those involving days in Access? A. Months are always 5 weeks B. Each month has the same number of days C. Months vary in length and may have 28, 30, or 31 days D. Days can not be calculated in months Answers: 1-B; 2-C; 3-D; 4-B; 5-D; 6-C; 7-B; 8-D; 9-A; 10-C 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 how to perform date and time calculations in Microsoft Access, including working with whole days as well as fractions of a day such as hours and minutes.Before getting started, it is important to understand how query criteria and calculated query fields work in Access. If you are not familiar with those concepts, I recommend checking out the introductory videos available through the links below. In Access, dates are actually stored as numbers, where a value of 1 represents one entire day. This allows us to do some interesting calculations. For example, if you have a date field, adding 1 advances it to the next day, while subtracting 1 moves it to the previous day. If you want to add a week, simply add 7. For 30 days, add 30, and so on. To demonstrate, I set up a query using a table with just an ID and a date field. I created a calculated query field with an expression that adds 30 to the date. When you run this query, you'll see that all the resulting dates are 30 days after the original field's date. This same process works well when calculating due dates, such as for invoices. You can create a due date by adding 30 days right inside the report using a calculated control source in the text box. For instance, if your field is called OrderDate, the expression OrderDate+30 will automatically display a due date 30 days ahead. You can also determine the number of days between two dates by subtracting one from the other. For instance, take an end date and subtract a start date; the result is the number of days between them. It is important to remember to put the later (future) date first in the expression to get a positive result. When you run a query like this, Access will return the day count for each row. Access allows you to use date values in criteria with either static dates (which must be surrounded by pound symbols, for example #2/1/2022#) or dynamic values using the Date function, which brings in the current date from your computer's system clock. This enables filters such as "all records with dates before today," "less than seven days from now," or "less than 30 days from now." As an example, if you want to see all orders due within the next 30 days, a query can display the records where the date matches that condition. If you prefer to see future dates, you can flip the condition to "greater than today's date," which will produce a list of all upcoming records. Because Access treats one day as the value of 1, you can use fractions of 1 to represent parts of a day. For example, adding 1 divided by 24 will result in one hour. Similarly, dividing by 24 times 60 gives you one minute. The calculation can be handled directly by Access, so you simply need to set up the proper arithmetic. In a query example, I show how adding 1 divided by 24 to a time value results in the time plus one hour, and how dividing appropriately produces a minute or even a second. These techniques are well suited for calculating days, weeks, hours, minutes, and seconds. However, for entire months, the calculation is a bit more challenging, since months have varying numbers of days. Access includes useful functions like DateDiff and DateAdd for working with months, quarters, years, and more. These are essential if you need to calculate the number of whole calendar months between two dates, or to add a certain number of months regardless of the number of days in each. To sum up, you can accomplish a wide variety of date math tasks in Access using simple arithmetic or built-in functions, depending on your needs. If you want step-by-step guidance on everything discussed here, a complete video tutorial is available on my website at the link below. Live long and prosper, my friends. Topic ListAdding days to date fields in Access queriesCalculating future dates using calculated fields Finding difference in days between two dates Using date values as query criteria Working with static and dynamic date criteria Calculating dates relative to the current date Filtering records by upcoming or overdue dates Adding hours, minutes, and seconds to date fields Understanding fractional date values for time calculation Handling date criteria with date functions like Date and Now Brief overview of DateDiff and DateAdd for months and years |
||||||||||||||||||||
|
| |||
| Keywords: FastTips Access date calculations, Calculating Dates, Date Calculation in Microsoft Access, How do you calculate dates in Access, How do I calculate the number of days between two dates in Access, How do you add 30 days to a date in Access, Add to or subt PermaLink Date Math in Microsoft Access |
