446,327 Members | 1,805 Online
+ 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
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

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

 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" 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" 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:... 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

 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

 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" 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.