By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Help!! Time Calculations.

P: n/a
Hi all,
I'm creating a TimeSheet Database, I need to calculate how many hours
the Employee works.The problem is that when they enter the time, it
doesn't calculate the minutes, it just calculate hours.it rounds up to
6 and not 5:30.
I'm using this formula:

Me.Hours = (DateDiff("h", Me.Start, Me.Stop))

Ex:
Start Stop Hours
08:30AM 02:00PM 6 <it should be 5 hours and half
I would be grateful, if someone can help me.

Thanks in advanced.
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
cs********@partners.org (christian) wrote in
news:e1**************************@posting.google.c om:
Me.Hours = (DateDiff("h", Me.Start, Me.Stop))

Ex:
Start Stop Hours
08:30AM 02:00PM 6 <it should be 5 hours and half


DateDiff returns, as an integer, the number of interval (in this case the
interval is hours) boundaries crossed. Perhaps you could specify minutes and
divide by 60.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2

P: n/a
Try using the minutes ("n") argument instead and then divide the result by
60 to get hours as a decimal.

Mike Storr
www.veraccess.com
"christian" <cs********@partners.org> wrote in message
news:e1**************************@posting.google.c om...
Hi all,
I'm creating a TimeSheet Database, I need to calculate how many hours
the Employee works.The problem is that when they enter the time, it
doesn't calculate the minutes, it just calculate hours.it rounds up to
6 and not 5:30.
I'm using this formula:

Me.Hours = (DateDiff("h", Me.Start, Me.Stop))

Ex:
Start Stop Hours
08:30AM 02:00PM 6 <it should be 5 hours and half
I would be grateful, if someone can help me.

Thanks in advanced.

Nov 12 '05 #3

P: n/a

Thank you very much.

Could you help me with the formula, because I don't really know where to
divide by 60.
Thanks once again
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
Thank you very much. Can you show me How to do that?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Me.Hours = DateDiff("n", Me.Start, Me.Stop) / 60

This will change the DateDiff to calculating minutes and divide that result
by 60. The result will be a decimal representation of the elapsed time (i.e.
5.5 not 5:30). If you want the latter format:

intIntervalMinutes= DateDiff("n", Me.Start, Me.Stop)
Me.Hours = intIntervalMinutes \ 60 & Format(intIntervalMinutes Mod 60,
"\:00")

The backslashes are intentional. The first one is "integer division" and the
second one tells the format command to treat the : as a displayed character
and not part of the format mask.

--
Wayne Morgan
Microsoft Access MVP
"cristian sepulveda" <cs********@partners.org> wrote in message
news:40***********************@news.frii.net...

Thank you very much.

Could you help me with the formula, because I don't really know where to
divide by 60.
Thanks once again
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #6

P: n/a
Thank you very much for the formula.
But when I run the formula, I get this message.
What do you think it could be:

Run-time error '-2147352567 (80020009)':
the value you entered isn't valid for this field.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #7

P: n/a
What data type is the field? Is there an input mask?

--
Wayne Morgan
Microsoft Access MVP
"cristian sepulveda" <cs********@partners.org> wrote in message
news:40***********************@news.frii.net...
Thank you very much for the formula.
But when I run the formula, I get this message.
What do you think it could be:

Run-time error '-2147352567 (80020009)':
the value you entered isn't valid for this field.

Nov 12 '05 #8

P: n/a
cs********@partners.org (christian) wrote in message news:<e1**************************@posting.google. com>...
Hi all,
I'm creating a TimeSheet Database, I need to calculate how many hours
the Employee works.The problem is that when they enter the time, it
doesn't calculate the minutes, it just calculate hours.it rounds up to
6 and not 5:30.
I'm using this formula:

Me.Hours = (DateDiff("h", Me.Start, Me.Stop))

Ex:
Start Stop Hours
08:30AM 02:00PM 6 <it should be 5 hours and half


If you get any closer, the answer will bite you. Get the difference
in minutes and then divide by 60. Then you'll get some number, like
5.333..., which you can round to some number of places.

Me.Hours =Round((DateDiff("n",Me.Start, Me.Stop))/60,2)
Nov 12 '05 #9

P: n/a

yes, the data type is (Number) and it has an input mask of (Short time.)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #10

P: n/a
christian wrote:
Hi all,
I'm creating a TimeSheet Database, I need to calculate how many hours
the Employee works.The problem is that when they enter the time, it
doesn't calculate the minutes, it just calculate hours.it rounds up to
6 and not 5:30.
I'm using this formula:

Me.Hours = (DateDiff("h", Me.Start, Me.Stop))

Ex:
Start Stop Hours
08:30AM 02:00PM 6 <it should be 5 hours and half

I would be grateful, if someone can help me.

Thanks in advanced.


If you are assured the times won't exceed 23 hours and 59 minutes you can
use the timeserial() function
? Format(timeserial(0,90,0),"hh:mm")
this will echo 01:30 The 90 is the number of minutes. If it exceeds the
largest part of time, it does the calcs for you. 90 minutes exceeds 59
minutes to it calcs the hour and minutes.

If you are using numbers, you can subtract. If using times, use
datediff() and calc on minutes.

Please read on-line help for examples on all of the functions I, and
others, have provided you.
Nov 12 '05 #11

P: n/a
Ok, this is probably where the problem is. What type of Number (Long
Integer, Integer, Double)? This will be at the bottom of the table design
window when you have the field selected. What you have here is an elapsed
time, not an actual time. You can store the value as a number (i.e. 5.5) or
as text (i.e. 5:30). You could set it up as a date/time field as long as you
don't go beyond 23:59, if you do it will revert back to 0.

--
Wayne Morgan
Microsoft Access MVP
"cristian sepulveda" <cs********@partners.org> wrote in message
news:40***********************@news.frii.net...

yes, the data type is (Number) and it has an input mask of (Short time.)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.