Microsoft Access Date & Time Calculations

that's awesome ... thank you, Sir ...

make an updated video on this please

Exactly what I needed

can you please show us how to find the number of days between consecutive records. i.e. there is one column of dates and I would like to write a function or a formula the returns the number of days between records (rows). and for the last date in the group, it should return the number of days till now().
thanks

Thanks

How can i enter in table hh:mm only without date

Hello... Thanks for your tutorials. I have the period taken e.g. 8 months for an item borrowed on 01/02/2018. how will I calculate the date when the Item should be returned?

Sir how to calculate total hours from all fields?

Hi there, i am new to access. I am trying to calculate a late shift time, ex. starts at 9pm and finishes at 5am in the next morning. If I use the formula then I get no of hours worked in negative figure( -8 hours). How do I over come this. Thank you for your help in advance.

very informative, thanks

SELECT Avg(Date()-[DRIVER]![date_of_employment]) AS [Duration (days)]
FROM DRIVER;

Thank you.

thank you very much for this highly useful video

Dude, you're a life saver. I subscribed because you have knowledge. THANK YOU SO MUCH SIR! THANK YOU!

Thank you so much you are great

I cannot see the date calculation where u did the average of the dates I am a but confused as to how to get it

How can i compare a date from a column with system date or current date
and can i delete a row using if condition

Thank you so much!!!!!!!!!!!!!!!!!

Hi! Thanks for your tutorial. How would you write correct expression to calculate over time hours work time between PM and AM. The expression I have works fine for AM to AM, PM to PM, and AM to PM. The problem is from PM to AM. For example, 7pm time in, and time out is 2am, Access is giving me 17 hours work, when the correct amount should be 7 hours. Help??

thank you 

sir

Thanks!! It helped a lot!!

Thanks................. Exactly what am I looking. 

very helpful, but how would you calculate the exact hours worked, taking into consideration breaks, lets' say, lunch break, and 2 smaller breakes, 15 mins each.

I know I'm so close with building an expression to calculate sick-time for an employee that works 8 hour days.  What formula should I use if the person is out for a day and a half.  for example 12 working hours.  If I use the 24 hour cycle in my expression I come back with 28.  I hope this makes sense.

Dear Sir,
Let me ask you some question, i would like to calculate the expire date for renewal day that client have to come for renew in each year, each year client have to pay for renewing their products year by year... so how can i create the Queries code or VBA code for the Ms Access 2003 or 2007

1. how to determine the expiry date
2. show reminder on the Access report by highlight with the color as Red, Yellow, Green when the expiry date is coming before 30 left

Thank you,
Best Regards
Anousone Phaiyasane

Thanks!

Very helpful tutorial. I m working on a project, that is dealing with the services of crew in a shipping company. In one of the tables, I have 3 fields: : sign on date : sign off date : difference of the below dates(in months) but where there is no sign off date yet I would like to calculate the difference between today and sign on. I have tried couple of expressions with no luck. such as IIf(signoff,today(),signoff-signon,today()-signon). ALso "today()" expression doesnt work in access 2010

Just about anything is possible in Access, but you would need to play some tricks to get this to work right. You could enter JUST a time in a 2nd field and then use some event programming to say that if the date is missing look at the previous field and grab that date + 1 day, but that would involve a little programming.

Richard, is there a way around entering complete date/time format for schedules that span 2 days ie., 6pm on 09/05 - 2am 09/06. Can I enter a date in it's own field(once) then enter timeIn and timeOut in their respective time fields, then calculate the total hours worked?

hi! I am a beginner 'access fanatic'.. i am doing this payroll system and i want to do one table displaying the daily timesheet of employees. I want to put in field name 1-31 (31 days in a month) with days (Mon-Sun). Is there a shortcut way for this? Thank you in advance.

Sounds like you need a simple set of QUERY PARAMETERS to limit your range of records returned. See this tutorial: 599CD(dot)com/XLA3E0

You a boss! thanks God i found you been searching the net for long now. If it's not to much for you can you make or where can i find your video that shows how i can take the hours of a month like week 1 i worked 20 hours week 2,10 and so on and when i choose calendar from a specific date to date it show me the total hours of week 1-4(month). Hope you understand me sorry for the bad English not from USA. Thank you so much

Your a BOSSS! thanks God I found ja

It works pretty much the same way. You're just adding 1 for each day.

IN EXCEL I USE DAYS+DATE I GET THE NEW DATE, HOW DO I DO THAT IN ACCESS IN QUERY FIELD.THANKS FOR HELP IN ADVANCE

Glad you like it.

That's what this video shows you how to do. :)

Hi Richard while i was creating a table my teacher said to me to make a difference or somthing like that on date manufacture and expiration date i didnt get the question could you please help me

This is PERFECT! Exactly what I needed to get total hours worked on my database! Thank you so much for making this tutorial!

Thanks Richard, i rectified my queries due to wrong spelling in parameter the reason i dont get the right answer..thank you so much for giving advise

That's exactly what this tutorial shows you how to do. :)

Hi Richard could you please help me out how to make expression in queries, to get right days between two months. example August 25 2012 minus September 20 2012 and days should be 27days total..hope to hear from you soon you can direct email to me as [email protected] thank you so much

Perhaps if I get time I'll put together a tutorial on this... it's a bit more complicated than just using the NETWORKDAYS function in Excel. If you need it in a hurry, look for the TechHelp link on my web site and submit your request there.

Hi, I need to calculate time taken from the request received date to order creation date. Calculation is : Total Days= Order date- Request date - (weekend + holidays). This is similar to the network days in Excel. Could you help me with finding out total days taken excluding holidays and weekends. Tony

thnx so much!