Microsoft Access Number of Days Between Two Dates - DATEDIFF Function - Date Difference - TechHelp

In this Microsoft Access tutorial, I'll show you how to calculate the number of days difference between two date fields. We'll use two methods: direct subtraction of one date from another, and the DATEDIFF function. You can also use DATEDIFF to show the difference in weeks, months, years, and so on.
RESOURCES:
Access Expert 27 & 28:
599cd.com/XF2T7
Calculating Age:
599cd.com/X9LHJ
Query Calculated Fields:
• Microsoft Access Calcu...
Access Beginner 5 - DATE() Function:
599cd.com/XF3R6
Records Between Two Dates:
• Find Records Between T...
Excel DATEDIF:
• Microsoft Excel Calcul...
Access Date & Time Calculations:
• Microsoft Access Date ...
Email:
[e-mail]
Your Questions Answered: 599cd.com/TechHelp
FREE Access Level 1: 599cd.com/X113
$1 Access Level 2: 599cd.com/X322
Richard's Blog: 599cd.com/blog
Access Learning Zone Facebook Group: / accesslearningzone
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
www.microsoft.com/en-us/micro...
LOOKING FOR MORE LESSONS?
Access Expert 27 & 28 cover:
- Now, Date, Time Functions
- Query Data in a Variety of Date Ranges
- Date & Time Mathematics
- Two Digit Year "2030" Problem
- List of Upcoming Birthdays
- Aged Accounts Receivable Report
- Display Times as 8:30, 8.5, 8h 30m
- Timesheets Spanning Midnight
- Calculating Someone's Approximate Age
- Break Apart Dates with Day, Month, Year
- Calculate Day of the Week with WeekDay
- DateAdd - What's 2 months from today?
- DateDiff - How long until loan paid?
- DatePart - Is order from last quarter?
- DateSerial - Find last day of next month
- Display Ordinals: 1st, 2nd, 3rd, etc.
- Calculate someone's exact age
- List of birthdays for next month
599cd.com/XF2T7

[@thomascorbett7545]

Thank you Richard, this is exactly what I needed. Love your classes, friendly, easy and applicable right away.

[@599CD]

Glad you like them!

[@jenstercastro9694]

Is it possible to make it in MS Access 2013?

[@jenstercastro9694]

@@599CD is it possible?

[@RonAbrams]

Thank you Richard stay positive hope your heath improves

[@599CD]

Thank you.

[@venkatk4703]

Excellent !! This is what I want .

[@599CD]

Glad to help

[@andreiagraca9141]

Very, very helpful. Thank you!

[@599CD]

You're welcome!

[@sagrodat]

Thank you for this sir, just what I needed

[@599CD]

Glad it helped

[@cambodeeritnews5629]

i love your tutorial video. God bless.

[@599CD]

Thank you! 😊

[@jessiesolijon3323]

Thanks for this sir!

[@599CD]

Welcome!

[@mohankrishnagc8967]

Dear Richard, Thank you for all your videos. I am viewing all your videos. It's really good to develop our skill set. Presently i am not able to displaying time zone based on the user selection I need help regarding setting the time according to the user's request. Based on User selection in the drop down list time should be display Ex: Bangalore-Bangalore Time zone New York - New York Time zone London - London Time zone San Francisco- San Francisco Time zone Thank you for your help... Regards, Mohan

[@599CD]

Working across time zones can be tricky. I'll try to put together a lesson on this in the near future.

[@thudeets]

thumbs up

[@599CD]

Thanks!

[@chrisbezant4760]

Hello Rick great content as usual but I am struggling with these full screen views. I much prefer your traditional small area views that are in all your courses.

[@599CD]

Yeah, sorry about that Chris. Had to join the 21st Century though and go HD. I think the videos are much clearer now.

[@user-jr3gd6rl8m]

What if you want to subtract two dates from within your table? For example, date received and date sent, I want to calculate the days difference between date received and date sent.

[@599CD]

599cd.com/ask

[@carlrigefsky9193]

I'm looking to calculate age as of 12/31 current year. Is there a way to calculate without having to hard code 12/31/yyyy? Thanks

[@599CD]

You could say: MyStr = "12/31/" & Year(Date()) MyDate = CDate(MyStr)

[@johnprins7453]

Good day Richard. I need to do a calculation between two dates: I have a delivery date and a cast date. The answer must tell me when 70% of the time between these dates has gone by and the item has not been cast yet

[@599CD]

Subtract date1 from date2. That will give you the number of days between the two dates. Now multiply that by 0.7 and then add that number to date1. That will tell you your target date.

[@_primedits614]

Is there any way to calculate the year differences instesd of the day?

[@599CD]

You mean like 599cd.com/Age?

[@f-dtpww5487]

Is there a way to use the DateDiff to display the difference in 2 dates in years, months, weeks, days, hours & mins in one string? Thx

[@599CD]

Not easily. I do have a function that I wrote for this, however: 599cd.com/HowLong

[@pinkypaler3067]

Hello Richard I was wondering if it is possible to merge the Years Months and Days into this kind of format 1y3m2d just like this because in excel I was able to do this but how on access hoping you will able to help me thank's in advance.

[@599CD]

Sure. Just use the Format function and some concatenation.

[@UsmanGhani-de1mz]

Sir, i watch your videos regularly, i have searched Ms.access two date range with third text box query i mean specific person data but never found, i think no one worked on this can you make a video on it?

[@599CD]

I'm not exactly sure what you're trying to do, but if you want me to make a video, submit it via the TechHelp page: 599cd.com/TechHelp - that's the best way to get on the list.

[@BanditFX7]

Nice topic ! So How Extract Day Name from date field ?????

[@599CD]

Easiest way is the use the FORMAT Function: Format(MyDate,"dddd") will give you "Wednesday". I cover this in Access Expert 11: 599cd.com/X2VJN

[@BanditFX7]

@@599CD Thk's very much!

[@YousafKazmi12]

Dear Sir I want to calculate age in access report as "01 years, 2 Months, 3 Days" format

[@599CD]

I cover this in my Access Expert 28 class. I actually wrote a function called HowLong which can return values like "1 year, 3 months, 4 days." 599cd.com/XACX28

[@cambodeeritnews5629]

i want access office like yours. where can i get it?

[@599CD]

I have a Microsoft 365 for Business subscription. amzn.to/3alIJLm

[@ryeshegabriel]

hello, im using ms access2007, and using datediff, but everytime i run a command im getting "Undefined function 'DateDiff' in expression" i hope u can help me, thanks

[@599CD]

Well, DateDiff has been around for ages, definitely since 2007. How/where are you using it?

[@chunglee5626]

Hi how are you? I have question see if you could help me on Microsoft Access. I got two tables, one is Customer, one is current month order, how could I find out customers do not order this month. How do I set the criteria on the customer table to display those one not order this month. Hope you could help.

[@599CD]

Doing it by month would be more difficult than just saying "show me customers who haven't ordered in 30 days." Set up an aggregate query to show MAX of the order date, and then use some date math like (Now()-OrderDate) to get the number of days since they last ordered. This is actually a good question for a video. Maybe I'll add it to my list...

[@padhichittaranjan11]

Sir, I want to calculate the Year, Month & Days from Date of Birth. Please explan the way to get actual age. Regards

[@599CD]

599cd.com/age

[@MalikBabarHasan60855]

please tell me how to get total numer of a given month as in ms excel EOMONTH

[@599CD]

I actually have a video on this coming soon.

[@MalikBabarHasan60855]

Thanks great sir. @@599CD

[@599CD]

599cd.com/EOMONTH

[@DP-tn8wo]

dd: DateDiff("w",[Change Start Date],[Date Logged]) ..........want to work out the difference in workday (5day) but its not working on MS Access 2007, help please Change Start Date 06/06/2021 Date Logged = 20/04/2019

[@599CD]

DateDiff with a "w" gives you whole 7-day weeks, not 5-day work weeks.