424,509 Members | 1,689 Online
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
4 Replies

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

 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 IN             OUT                txtHours 7:15          11:00                 3:45 Expand|Select|Wrap|Line Numbers Private Sub txtBoxOUT_AfterUpdate() Dim intDiffInMin As Integer Dim txtIN As TextBox Dim txtOUT As TextBox Dim intHrs As Integer Dim intMins As Integer   Set txtIN = Me![txtBoxIN] Set txtOUT = Me![txtBoxOUT]     If Not IsNull(txtIN) And Not IsNull(txtOUT) Then     If IsDate(txtIN) And IsDate(txtOUT) Then       intDiffInMin = DateDiff("n", txtIN, txtOUT)       intHrs = Int((intDiffInMin / 60))       intMins = intDiffInMin - (intHrs * 60)         Me![txtHours] = CStr(intHrs) & ":" & Format\$(intMins, "00")     End If   End If 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