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

time sheet form with datediff function

100+
P: 107
Hello,
I am now working on timesheet form users can input in time and out time.
I wanted to have a txtbox called hours for each day that would calculate the hours.
I have tried the datediff("h", me.txtboxIN, me.txtboxOUT) and "n"
I am able to get the hours or the minutes - but cannot show them both together.

I thought with the minutes there might be a way to divide and show hours and minutes.
If a person came in at 7:30 am and left at 11:00 am -textboxHOURS should reflect 3.50 hours.
any ideas
thank you
Jul 29 '08 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
Find the minutes then divide by 60:
Expand|Select|Wrap|Line Numbers
  1. datediff("n", me.txtboxIN, me.txtboxOUT)/60
To round to 2 decimals (I think)
Expand|Select|Wrap|Line Numbers
  1. Round(datediff("n", me.txtboxIN, me.txtboxOUT)/60,2)
Linq ;0)>
Jul 29 '08 #2

ADezii
Expert 5K+
P: 8,601
It's too close to bedtime, and I'm probably making this more complicated than need be, but assuming a Text Box on your Form is named txtHours, the following code placed in the AfterUpdate() Event of txtBoxOUT will produce the difference in HOURS:MINUTES Format, namely:
Expand|Select|Wrap|Line Numbers
  1. IN             OUT                txtHours
  2. 7:15          11:00                 3:45
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtBoxOUT_AfterUpdate()
  2. Dim intDiffInMin As Integer
  3. Dim txtIN As TextBox
  4. Dim txtOUT As TextBox
  5. Dim intHrs As Integer
  6. Dim intMins As Integer
  7.  
  8. Set txtIN = Me![txtBoxIN]
  9. Set txtOUT = Me![txtBoxOUT]
  10.  
  11.   If Not IsNull(txtIN) And Not IsNull(txtOUT) Then
  12.     If IsDate(txtIN) And IsDate(txtOUT) Then
  13.       intDiffInMin = DateDiff("n", txtIN, txtOUT)
  14.       intHrs = Int((intDiffInMin / 60))
  15.       intMins = intDiffInMin - (intHrs * 60)
  16.         Me![txtHours] = CStr(intHrs) & ":" & Format$(intMins, "00")
  17.     End If
  18.   End If
  19. End Sub
Jul 29 '08 #3

100+
P: 107
thank you
missinglinq
I wasn't able to get yours working- but I think I've been trying too hard on this and may be I inputed syntax wrong
Adezi- I used your solution-IT WORKS
thank you both
Jul 29 '08 #4

P: 2
Try This

=DateDiff("n",field,field)\60 & ":" & Format(DateDiff("n",field,field) Mod 60,"00")

Shony
Jun 4 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.