Excel 2000 Macro Help - Working with Times

If the numbers are being entered as text then Excel would convert
TimeValue("06:30")
to  0.27 days which would be straightforward in converting to a differential

ie

Sub t()
timeinVAR = timesheet.timeIn.Value
timeoutVAR = timesheet.timeOut.Value
    totalhoursvar = TimeValue(timeoutvar) - TimeValue(timeinvar)
    If totalhoursvar < 0 Then
    'Add 12 hours
        totalhoursvar = 0.5 + totalhoursvar
    End If
    'Convert to days
    ActiveCell.Value = totalhoursvar * 24
End Sub


So if your numbers could be entered as "HH:MM" it would be easy. If your users are entering the numbers as "HHMM" the string needs conversion like below

Excel now works in days so the end result is multiplied by 24 to get it back to hours


Sub t()
  timeinVAR = timesheet.timeIn.Value
timeoutVAR = timesheet.timeOut.Value
    'These functions are converted to time with TimeValue by inserting a ":"
    newtimein = TimeValue(Left(timeinvar, Len(timeinvar) - 2) & ":" & (Right(timeinvar, 2)))
    newtimeout = TimeValue(Left(timeoutvar, Len(timeoutvar) - 2) & ":" & (Right(timeoutvar, 2)))
    totalhoursvar = newtimeout - newtimein
    If totalhoursvar < 0 Then
        totalhoursvar = 1 + totalhoursvar
    End If
    ActiveCell.Value = totalhoursvar * 24
End Sub

Somehow hit the submit button a little early, the last section of code had add 1 (24 hours) and it should have been 0.5 (12 hours) if the out time was less tham the in time. Sorry for the doubling up of the post..........

Sub t()
 timeinVAR = timesheet.timeIn.Value
timeoutVAR = timesheet.timeOut.Value
   'These functions are converted to time with TimeValue by inserting a ":"
   newtimein = TimeValue(Left(timeinvar, Len(timeinvar) - 2) & ":" & (Right(timeinvar, 2)))
   newtimeout = TimeValue(Left(timeoutvar, Len(timeoutvar) - 2) & ":" & (Right(timeoutvar, 2)))
   totalhoursvar = newtimeout - newtimein
   If totalhoursvar < 0 Then
       totalhoursvar = 0.5 + totalhoursvar
   End If
   ActiveCell.Value = totalhoursvar * 24
End Sub

Cheers

Dave

Brettdj

I have tried the code you suggested however I keep getting "Type Mismatch" when it reaches the NEWTIMEIN line.

I tried to define newtimein as a variable but with no success.

Suggestions???




Byundt:

Thank you for your suggestion. Unfortunately for the functionality I need I cannot use that solution. I just need to display the total hours the employee worked rather than display their start and finish time on the worksheet.

Any progress?

Guys I did the split points because I honestly can't remember exactly which of the solutions provided helped me most towards finding my answer.

I apologise for leaving the question open for so long.

Thank you for your help!!

Thanks for the grade.  :)

Cheers

Dave