VBA to check Time span with Mod function

[smd747]

I am checking Time spans on a spreadsheet like in times sheets. Many span both shifts day and night.
I used the Mod(C2-B2,1) format cell [h]:mm

What I am tiring to do is create vba macro which will prompt me for the first cell then second cell for the Mod formula and answer would show me in a message box this answer would have to be formatted [H]:mm

My first attempt was this.

Sub CheckTime()'
' CheckTime Macro


    ActiveCell.Offset(0, -3).Range("D1,B1,A1,D1").Select
    Selection.FormulaR1C1 = "=MOD(RC[-2]-RC[-3],1)"
End Sub

Problem is the cells are not always aligned.

any suggestion as to the best way?

Thanks

 

[Jerry Sullivan]

Hi friend,

You could use the Application.InputBox function to allow the users to select the two cells with the start and finish times.

At its most basic, that could look something like this.

Sub Interval()
    Dim cStart As Range, cFinish As Range
    Dim sResult As String
    
    Set cStart = Application.InputBox("Pick Cell with Start Time:", Type:=8)
    Set cFinish = Application.InputBox("Pick Cell with Finish Time:", Type:=8)
    sResult = Evaluate("MOD(" & cFinish - cStart & ",1)")
    MsgBox Format(sResult, "h:mm")
End Sub

You would probably want to add some error handling for any exceptions that you can think of (user cancels, or picks more than one cell, or picks a cell without a valid time).

 

[smd747]

Hello Jerry

Perfect what a time saver, it prompts and allows me to select any cell prompting for the start time and Finish Time. This is just what i was looking for.

I added for error code On Error Resume Next, before End Sub, thinking that would kill the macro but that does not work. Still struggling with the VBA a little

Thanks once again for the help Jerry

 

[smd747]

Figured the error handler out:

Sub Interval()    Dim cStart As Range, cFinish As Range
    Dim sResult As String
  On Error GoTo Errhandler
    Set cStart = Application.InputBox("Pick Cell with Start Time:", Type:=8)
    Set cFinish = Application.InputBox("Pick Cell with Finish Time:", Type:=8)
    sResult = Evaluate("MOD(" & cFinish - cStart & ",1)")
    MsgBox Format(sResult, "h:mm")
Errhandler:


      ' If an error occurs,
      MsgBox "Wrong Time format entered.Please check format."


  End Sub

Thanks again Jerry for the help with the Vba

 

[Jerry Sullivan]

Good progress, but a few gaps in that try.

First, you need an Exit Sub before the errorhander otherwise your code will continue and report an error when there is not one.

Secondly, you might want to check for blanks or values greater than 1 which would not necessarily cause an error, but might give incorrect results.

Maybe....

Sub Interval()
    Dim cStart As Range, cFinish As Range
    Dim sResult As String
    
    On Error GoTo ErrorHandler
    Set cStart = Application.InputBox("Pick Cell with Start Time:", Type:=8)
    If Not (IsNumeric(cStart) And cStart <= 1 And cStart >= 0 And Not IsEmpty(cStart)) Then GoTo ErrorHandler
    Set cFinish = Application.InputBox("Pick Cell with Finish Time:", Type:=8)
    If Not (IsNumeric(cFinish) And cFinish <= 1 And cFinish >= 0 And Not IsEmpty(cFinish)) Then GoTo ErrorHandler
    On Error GoTo 0
     
    sResult = Evaluate("MOD(" & cFinish - cStart & ",1)")
    MsgBox Format(sResult, "h:mm")
    Exit Sub
ErrorHandler:
    MsgBox "An error occurred"
End Sub

 

[mikerickson]

If an input box is set to a non-range data type, clicking on a cell and pressing enter will enter the value in that cell into the input box.
Try this

Sub test()
    Dim uiStartTime As Double
    Dim uiEndTime As Double
    Dim Duration As Date
    Dim strPrompt As String
    
    Do
        uiStartTime = Application.InputBox("Select the cell with start time or enter it.", Type:=1)
        If uiStartTime = 0 Then Exit Sub: Rem cancel pressed
    Loop Until 0 < uiStartTime
    
    Do
        uiEndTime = Application.InputBox(strPrompt & "Select the cell with end time or enter it.", Type:=1)
        If uiEndTime = 0 Then Exit Sub: Rem cancel pressed
        strPrompt = "End time must be after the start time." & vbCr & vbCr
    Loop Until uiStartTime <= uiEndTime
    
    Duration = CDate(uiEndTime - uiStartTime)
    MsgBox "Duration is " & Format(Duration, "h:mm:ss")
End Sub

 

[Jerry Sullivan]

Hi Mike,

That's a good idea to allow inputs other than ranges.

There are a few scenarios that my suggested code tried to address that might want to be handled.

1. The OP said that sometimes the time spans overnight. Your suggested code will work if the cells selected are using consectutive date values. For a timesheet that might not be the case. If the user selects start: 10:00 PM and finish:1:00 AM the result should be 3:00

2. If the user selects invalid numbers like Start: 9 finish 9.5, your code will return 12:00.

 

[mikerickson]

1) Start time = "10:00 PM" StopTime = "11:00 AM"
I'm not sure if the user correctly entered a 13 hour interval or if they mis-entered an 11 hour interval.
The below code does not validate to insure that Start < Stop, but corrects it.

2) In Excel serial dates, the interval between 9 and 9.5 is the interval between 1/9/1900 00:00:00 and 1/9/1900 12:00:00.
12 hours is the correct answer.

Sub test2()
    Dim uiStartTime As Double
    Dim uiEndTime As Double
    Dim Duration As Date
    
    Do
        uiStartTime = Application.InputBox("Select the cell with start time or enter it.", Type:=1)
        If uiStartTime = 0 Then Exit Sub: Rem cancel pressed
    Loop Until 0 < uiStartTime
    
    Do
        uiEndTime = Application.InputBox(strPrompt & "Select the cell with end time or enter it.", Type:=1)
        If uiEndTime = 0 Then Exit Sub: Rem cancel pressed
        
        If uiEndTime < uiStartTime Then
            uiEndTime = Int(uiStartTime) + 1 + uiEndTime: Rem overnight correction
        End If
    Loop Until uiStartTime <= uiEndTime
    
    Duration = CDate(uiEndTime - uiStartTime)
    MsgBox "Duration is " & Format(Duration, "h:mm:ss")
End Sub

That is one of the reasons that a userform is preferred for date/time entry over an input box.

 

[smd747]

Wow, what an education. You’ve taken my concept to a whole new level and offered me variables I had never even considered. (But need to) Thanks guys for all your direction. I tested both of them with all different time spans and they work fantastically. The time spans I check are all over, like, 6:00 AM to 2:00PM, 7:00 AM to 4:00 PM, 5:00 PM to 2:00 AM and a 6:00 PM to 3:00 AM. I use your VBA as a check for times I question or want to proof.

Is it possible? To add another input box to add a lunch break, either 0.50 or 1:00 (half hour or hour, or should it be 0.30) and deduct it from the total.

What I do now is run the macro, look at the break and deduct from total of the message box. I do this as a check

Thanks again, I am forever grateful for the direction and help in expanding my vba knowledge base.

Thank you

 

[Jerry Sullivan]

2) In Excel serial dates, the interval between 9 and 9.5 is the interval between 1/9/1900 00:00:00 and 1/9/1900 12:00:00.
12 hours is the correct answer.

Mike, I understand the reason why 12:00 is returned and that it is mathematically correct. I was suggesting that if the user is supposed to restrict the inputs to cells that have times between 1/1/1900 00:00:00 and 1/1/1900 11:59:59 (0 to 0.999), then the code might respond to an entry greater than 1 with a warning or error message.

Also, if user wants to take advantage of the feature that allows entry of the value into the InputBox instead of picking a cell, IMO it's more intuitive that they would enter 9.5 or 9:30 for 9:30 AM than the serial value 0.39583

The handling of a 0 entry is also problematic. It could be the user pressed cancel, or picked a blank cell which should stop the execution; but it could also mean the user selected 0:00 for a start or finish time of midnight.

That is one of the reasons that a userform is preferred for date/time entry over an input box.

I agree - particularly with the addition of smd747's idea to incorporate lunch breaks. The InputBox becomes more cumbersome.

Stepping back to the big picture, since this sounds like it is being used for checking by smd747 instead of data entry by the people completing the worksheets, I wonder if the need for this checking could be eliminated through the use of an improved timesheet template.

The OP indicated that the problem is that the Start Finish cells are not always aligned - perhaps they should be.
A well designed timesheet could use Data Validation to ensure only valid entries, have a simple means of noting break times, and locked formulas on a protected worksheet to calculate the elapsed times.

smd747, do you have the option to make revisions to the timesheet template?
That might be a better way to attack the problem than an improved method of checking.