DateDiff Function.......HELP PLEASE 1

[sedilson]

Okay help please!

I have asked this before but it still hasnt solved it

I currently have 3 textboxes

txtSignIn, txtSignOut, txtTotalHrs

the user enters a signIn time and a SignOut time and i would like to the calculate time including minutes not just hours between the two textboxes.

So if I Signed In @ 8:35 AM and SignOut @ 1:50 PM my totalhours text box should display the correct hours and minutes worked.

Now the other problem is when the user works the midnightshit and it turns into a new day....

So SignIN @ 10 PM and SIgnOut at 6AM (Next Day)


So far i'm using this

=DateDiff("h",[txtSignIn],[txtSignOut])

and so far it doesnt work for the midnighshift or does it return the minutes it just returnes the hours

Somebody please Help with this!

Thanks in Advance

 

[koresnordic]

change the "h" to "n" and it shows minutes (you can't get hours & mins). You will need to calculate the hours and mins (hours is int(min/60) with mins being mins -hours*60)). As regards to being the next day, assuming that nobody works 24 hours, then check if end time is before start time. If so then the number of hours is (24 hours minus the start time (to get the time worked before midnight) plus (24 minus the end time) (to get the hours worked after midnight).



Graham

 

[sedilson]

Can You show me an example of this please

 

[ReluctantDataGuy]

For elapsed times, I use a calculated value that has three components, days, hours minutes. In your case you only need hours and minutes. So here's an example:

Me.txtTotalHrs = (DateDiff("n", [txtSignIn], [txtSignOut]) / 60) & " hrs, " & (DateDiff("n", [txtSignIn], [txtSignOut]) - (DateDiff("n", [txtSignIn], [txtSignOut]) / 60) * 60) & " min(s)"

This gives txtTotalHrs the format: "7 hr(s), 58 min(s)" Consequently, its text, not a date - so keep that in mind.

Now about the shift causing an overnight date problem. If you just enter a time, Access (at least in Access 2000) assumes a date of 12/31/1899. You don't want that...

There's a couple of options. The simplest is to allow the user to click a button to enter the sign in and sign out times.

For example, the on click of cmdSignIn would be:
Me.txtSignIn = Now

That way you can use a General Date which includes date and time, and not force the user to enter the entire date and time manually. The above hours and minutes code should still work fine without modification if a general date is used instead of just a time.

This solution causes a problem though, if the user isn't allowed to modify the datetime. What if the user actually finished work 20 minutes ago, but stopped to BS with a coworker on the way out. When the user clicks the button, its not the real time he or she stopped working. In that case you can allow the user to modify the time, and Access should give an error message if the user doesn't enter the time in the right format. Of course, this opens up your application to fudging on time. If you wanted, you could store the actual date and time the sign in date and time were entered (not the user modified date and time) in a field in your data table. That way, you could go back later and find out how often, and by how much, sign in or sign out dates have been modified. That's if you or management is paranoid enough to go to those lengths...

Another way to do this is to have a hidden field on the form so that when the user types the time in to the sign in text box, an onlostfocus event procedure puts the current date in the non-visible field. Call it "txtSignInDate" (and "txtSignOutDate&quot

Then you'd change the hours/minutes calculation above as follows:

Me.txtTotalHrs = (DateDiff("n", CDate([txtSignInDate] & " " & [txtSignIn]), CDate([txtSignOutDate] & " " & [txtSignOut])) / 60) & " hr(s), " & (DateDiff("n", CDate([txtSignInDate] & " " & [txtSignIn]), CDate([txtSignOutDate] & " " & [txtSignOut])) - (DateDiff("n", CDate([txtSignInDate] & " " & [txtSignIn]), CDate([txtSignOutDate] & " " & [txtSignOut]) / 60)) * 60) & " min(s)"

NOTE: To use these, copy and paste them, I haven't used line continuation to clarify the examples because in the width of this response editing window, there are sections of the code that are too long to show on one line. If I put line continuation in to fit this window, it wouldn't work in code because you can't just break a line and use the continuation symbol "_" in any place you feel like. Like not in the middle of a function or field name, etc.