Microsoft Access Date & Time Calculations

[@JamieEnglish]

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

[@599CD]

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.

[@ALLCAPS]

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

[@599CD]

Welcome... oh wait.. you're ALL CAPS.... WELCOME!!!!!! :)

[@ALLCAPS]

@@599CD what do you mean? :p haha

[@599CD]

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.

[@red75dxb]

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

[@599CD]

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

[@JDMaxton1999]

Exactly what I needed

[@599CD]

Glad to help

[@aa5121]

thank you very much for this highly useful video

[@599CD]

You are very welcome. Glad you enjoyed!

[@rmuadji13]

thank you for this. very helpful!

[@wealth_curve7]

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

[@599CD]

Most welcome!

[@jmonn01]

Great Job, Thanks for the tip. How can you do this from mon to friday without doing each day at a time?

[@599CD]

Glad you like it.

[@djitobigD]

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

[@prgs01]

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?

[@PeacefulStreetsMadinah]

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

[@599CD]

Awesome

[@sukynessa]

Thanks!! It helped a lot!!

[@599CD]

Welcome

[@seratonaki]

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

[@shahlatifoptics2501]

Hello Sir, Please guide me on how can I use time in "IIF Function" in (Calculated Data Field - In Table Design View) with Comparison operators? "I want the output "8" if Time_Out is equal to or greater than 12:00:00 of midnight and "16" if Time_Out is equal to or Greater than 3:00:00 Am." I am using the following syntax but it's not working: IIf([Time_Out]>="12:00:00 AM","8",IIf([Time_Out]>="3:00:00 AM","16","0")).

[@599CD]

Use the HOUR() function 599cd.com/ACX11

[@ianleboo]

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?

[@599CD]

599cd.com/DateAdd

[@kidz_fun_love]

hello, i have one question. please help. if you see 2 row; TIME IN 1/1/2008 10:00:00 TIME OUT 1/1/2008 03:30:00 but show HoursWorked: 5.5 but if we calculate the actual time: 05:30 what wrong with this?? Please help.

[@599CD]

599cd.com/TimeSpent

[@sameerjamadar2125]

How do i calculate the working hrs if the shift started on the previous day at 18:00 and ends on next day 6:00 am?

[@599CD]

Use a complete date with time value

[@27oslec]

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.

[@davidtate2032]

I have a Start Time & End Time, when I put in my employee's in when they work until midnight (12:00 am) it shows -22 hours, but if I put in a long time, 11:59:59 pm it rounds off to the correct hours worked. I want to be able to put in just 12:00 am and have it calculate correctly...HELP I have tried everything

[@599CD]

599cd.com/TimeSpent

[@daveLFCfan2]

IF ITS 9:30PM EASTERN IN THE US WHAT TIME IS IT IN THE UK?

[@599CD]

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

[@waelzaini5091]

Thank you so much you are great

[@599CD]

I know. LOL. JK. You're very welcome

[@568tarudhruv9]

very informative, thanks

[@599CD]

You're welcome.

[@599CD]

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

[@alidemaj1866]

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

[@thompsonarlin]

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

[@cacingwong]

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

[@599CD]

You're welcome

[@zp3375]

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.

[@599CD]

Timesheets: 599cd.com/Timesheet

[@Granaldigirl]

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.

[@599CD]

It doesn't

[@twistedcookieswirl1504]

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.

[@599CD]

599cd.com/Timesheet

[@djitobigD]

Your a BOSSS! thanks God I found ja

[@ewilson378]

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??

[@599CD]

Use full date/time values.

[@InnerStar88]

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

[@599CD]

What do you mean by "last date in the group?"

[@InnerStar88]

@@599CD i.e. the last transaction date per customer when the report shows the results in groups.

[@amitpatil3567]

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

[@599CD]

Date()

[@599CD]

Delete Query

[@amitpatil3567]

@@599CD Dude seriously I asked that question 5years ago 🤣🤣 Now I am an devops engineer, Appreciate your efforts though

[@mmpnaidu]

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

[@MegaAnousone]

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

[@599CD]

599cd.com/ask

[@hangc10]

I got a task and see if you can accomblish this. I couldn't accomblish cause it's not like excel. A column = ID, B column = Name, C column = Donate, D column = Date, E column = Total (date). Task: Pretend that there's 5 record. All record donated as 5, 10, 15, 20, 25. Date are 9/1/11 (3 rows down) and 9/2/11 (2 rows down). On Column E; try to code so that it calculate row C only if row D = 9/1/11.

[@inducto]

thnx so much!

[@waqasahmedmangi1714]

Sir how to calculate total hours from all fields?

[@599CD]

Explain

[@mrigloooxer]

Thanks!

[@justforviews224]

make an updated video on this please

[@599CD]

K

[@TheMajed20100]

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

[@599CD]

What do you mean?

[@PinWorthyHomeFinds]

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

[@599CD]

I'm very sorry for taking so long to reply to your comment. I wasn't very good at keeping up with the comments 4 years ago, but I've been trying to make an effort to get back to everyone. If you still need help with this problem, please feel free to submit your question on my web site at 599cd.com/TH.

[@hmmm_mynk]

Thanks

[@599CD]

Welcome

[@charleselmore4707]

Thank you.

[@599CD]

You're welcome

[@KostoMax97]

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

[@599CD]

Doesn't look valid to me.

[@goinathan]

thank you sir

[@599CD]

Welcome

[@599CD]

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