Ms Access Time Calculations.

[shreyansghia]

Hello,

I am a new member and am glad to a part of this forum.

I am facing a slight problem while doing time calculations in MS access. I ve prepared a time sheet for my workers. Their regular time is till 17:00 hrs and anything above that is overtime. I am using the formula DateDiff("n",[Regular Time],[Time Out])/60. I am able to calculate the overtime if the worker finishes his work before the day ends. However , if he works untill for eg 2:00 AM , (i.e 9 hrs of over time) , i get a negative result.

Can this be worked out .

Thanks much in advance

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

[NeoPa]

Hello,

I am a new member and am glad to a part of this forum.

I am facing a slight problem while doing time calculations in MS access. I ve prepared a time sheet for my workers. Their regular time is till 17:00 hrs and anything above that is overtime. I am using the formula DateDiff("n",[Regular Time],[Time Out])/60. I am able to calculate the overtime if the worker finishes his work before the day ends. However , if he works untill for eg 2:00 AM , (i.e 9 hrs of over time) , i get a negative result.

Can this be worked out .

Thanks much in advance

Use the Mod operator. This will require ensuring that the value is an integer when processed but shouldn't be too much of a problem.
What are the units you're working in (IE. What does a value of 1 mean)?

[Scott Price]

Try changing your Time fields to Date&Time fields, such as by using the Now() function for logging the time in and time out.

Date/Time functions can be tricky because of the way Access stores and calculates time, but this should actually be a simple way to calculate the difference you are looking for.

Regards,
Scott

[shreyansghia]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

Sorry , i am new to this . I'll keep this in mind henceforth.

Thanks

[Scott Price]

Sorry, Adrian! Must have been posting at the same time again...

Regards,
Scott

[shreyansghia]

Try changing your Time fields to Date&Time fields, such as by using the Now() function for logging the time in and time out.

Date/Time functions can be tricky because of the way Access stores and calculates time, but this should actually be a simple way to calculate the difference you are looking for.

Regards,
Scott

For the time in and time out , the data type is set as Date/Time , however the result that i get is an integer , so that it calculates the exact overtime hrs.

U mentioned about the Now() function; is that VB . I ve no prior knowledge of VB. All i am using is tables and queries to do the above.

[NeoPa]

Sorry, Adrian! Must have been posting at the same time again...

Regards,
Scott

Don't be daft Scott! You have every right to post your ideas in here whether I choose to or not :)

That reminds me I need to get your "Expert" invite off to you as soon as. I'll get it out today if humanly possible :)

[NeoPa]

shreyansghia,
Have you looked at post #3?
If so, can you provide answers please.
An alternative approach is to say :

If datStart > datEnd then datEnd = datEnd + 1

where Date/Time fields store days as 1.
You then continue as before.

Of course, this can only be a problem if you don't store the Date/Time values fully in the first place :/

[Scott Price]

Thanks again, Adrian!

If you put this:

=Date() & " " & #17:00#

in the default value of your Regular Time field at the table level, it will automatically enter the current Date (8/24/2007, according to my computer) and the normal quitting time of 17:00 hours.

Depending on how you enter your Time Out data (i.e. manually through a text box, or by clicking a command button, etc.) you can add in the appropriate place

=Now()

You shouldn't have to change much more than that, as your fields are already being compared, and the calculation as you have it of the date/time should work out fine.

Have to run, and won't be back until this aft... Post more questions as needed, and someone will help :-)

Regards,
Scott

[shreyansghia]

Thanks again, Adrian!

If you put this:

=Date() & " " & #17:00#

in the default value of your Regular Time field at the table level, it will automatically enter the current Date (8/24/2007, according to my computer) and the normal quitting time of 17:00 hours.

Depending on how you enter your Time Out data (i.e. manually through a text box, or by clicking a command button, etc.) you can add in the appropriate place

=Now()

You shouldn't have to change much more than that, as your fields are already being compared, and the calculation as you have it of the date/time should work out fine.

Have to run, and won't be back until this aft... Post more questions as needed, and someone will help :-)

Regards,
Scott

Thanks for all the help . I know this might sound strange , but is it ok if i could mail my database to u (Scott) or NeoPa. I ve not been able to fix this thing.
If yes, i need ur email address.

Also just FYI , i am working on Access 2007. Also the records that i would be feeding in is not on daily basis , but rather, at the month end.

Thanks :-)

[Scott Price]

Thanks for all the help . I know this might sound strange , but is it ok if i could mail my database to u (Scott) or NeoPa. I ve not been able to fix this thing.
If yes, i need ur email address.

Also just FYI , i am working on Access 2007. Also the records that i would be feeding in is not on daily basis , but rather, at the month end.

Thanks :-)

Access 2007? What's that? :-)

Sorry, I'm on Access 2003, and I think NeoPa is also.

If you post more specifically what is not working, we can try to work you through what's wrong... Emailing the db to me isn't an option at this point, since I don't even have Access 2007.

Regards,
Scott

[Scott Price]

This link is to Allen Browne's website tips and links to A2007 articles. It will help you to read and understand some of the differences between the different versions.

http://allenbrowne.com/Access2007.html

Regards,
Scott

[shreyansghia]

This link is to Allen Browne's website tips and links to A2007 articles. It will help you to read and understand some of the differences between the different versions.

http://allenbrowne.com/Access2007.html

Regards,
Scott

Allright Scott,

Let me make one last attempt. I have 2 tables.

My 1st table has 2 fields: Worker Name and Worker Id.

In my 2nd table , i ve worker ID , work date, time in , time out, regular time Then i created a simple query having the fields mentioned in the 2nd table and plus the Overtime field which calculates the extra hrs worked.

In the design view of the query , in the field row i ve

 overtime:DateDiff("n",[Regular Time],[Time Out])/60  

I ve liked the 1st table with the query ( the link being worker id.)

Lastly, the codes that u ve mentioned above , where do i write them. In the field row of the design view? Or is it VB , i dont know VB at all . :-)

Thanks a lot.

[Scott Price]

Good morning!

The first one code that I mentioned will go in the default value row of your table design view for the field Regular Time.

Right click on your table, choose Design View. Left click once on the name of your field, at the bottom of the design view grid window you will see the properties for that field. Paste this:

=Date() & " " & #17:00#

in the Default Value.

The next question is, how do you enter your Time Out data? Do you manually type in what time the person leaves work? Do you have a combo box to choose a time selection? Do you have a command button that when you click, it enters a specified time?

Since you are doing this once a month, I suspect that you are typing these in manually. If that is the case, then you will only need to do one thing more: type in the date as well as the time: i.e. 8/25/2007 19:00

Any other questions, I'll be glad to try to explain a little more.

There shouldn't be any need to change the design of your query, as the DateDiff() function works with any flavor of date/time that you throw at it.

Regards,
Scott

[shreyansghia]

Good morning!

The first one code that I mentioned will go in the default value row of your table design view for the field Regular Time.

Right click on your table, choose Design View. Left click once on the name of your field, at the bottom of the design view grid window you will see the properties for that field. Paste this:

=Date() & " " & #17:00#

in the Default Value.

The next question is, how do you enter your Time Out data? Do you manually type in what time the person leaves work? Do you have a combo box to choose a time selection? Do you have a command button that when you click, it enters a specified time?

Since you are doing this once a month, I suspect that you are typing these in manually. If that is the case, then you will only need to do one thing more: type in the date as well as the time: i.e. 8/25/2007 19:00

Any other questions, I'll be glad to try to explain a little more.

There shouldn't be any need to change the design of your query, as the DateDiff() function works with any flavor of date/time that you throw at it.

Regards,
Scott

Hello Scott ,

Thanks a lot . It does work with your above guidance. However i had to make a few changes in my table and query. If you remember , i told you at the data is put once , at the end of the month. So if use the code

=Date() & " " & #17:00#

in the Default Value, then for all the values it would be the date when the data is feeded in. So to overcome this , i ve made another column in my table which would record the date when the worker exists the factory (This is required when the worker works even past midnight).

Thus my query has Work Date , Time In , Time Out ,Out Date,
Regular Time ( here i would have

[Work date] & " " & #17:00#

Another column which would be

[Out date] & " " & [Time Out]

.

And finaly i could use DateDiff to get the overtime hrs .

Thanks a lot . It was a great help :-)

[Scott Price]

Glad to be able to help, and thanks for posting back with what worked for you!

Regards,
Scott