Timespan Starting With Active Cell Value Using Input Box Value

[Rick Rothstein]

How could I hard code 60 which is 60 minutes lunch break or 1-hour

TimeDiff = Format(EndTime - StartTime, "h"" hours and ""m"" minutes""") - 60

did not work. I'm thinking I can have two buttons one for a 60 minute lunch break or 30 minute lunch break break to be deducted at the end. I hope I explained it better, sorry

The part I highlighted in red is the calculated value (the Format function displays the calculated value in a "formatted" way), so that is the value that the lunch break time should be subtracted from, but you cannot just subtract 60 for 60 minutes as 60 is not a time value (which is what StartTime and EndTime are), rather, you can use the TimeSerial function and let it construct the time for you for your given number of minutes. For example...

TimeDiff = Format(EndTime - StartTime - TimeSerial(0, 60, 0), "h"" hours and ""m"" minutes""")

I think I would modify the code to handle the break time separately in the MessageBox, otherwise the start/end times and displayed difference won't "add up". However, I wasn't sure how you would want to report the time difference, so I have given you two macros to choose from (pick the one with the output you like best)...

Sub GetStartEndTime1()
  Dim StartTime As Date, EndTime As Date, BreakTime As Long, TimeDiff As String
  StartTime = ActiveCell.Value
  EndTime = Application.InputBox("Select the cell with the ending time...", Type:=8)
  BreakTime = 60 'Not sure how you plane to calculate 30 or 60, but it goes here
  TimeDiff = Format(EndTime - StartTime, "h"" hours and ""m"" minutes with a " & BreakTime & " minute break.""")
  MsgBox "Start time: " & StartTime & vbLf & "End time: " & EndTime & vbLf & "Time difference: " & TimeDiff
End Sub

Sub GetStartEndTime2()
  Dim StartTime As Date, EndTime As Date, BreakTime As Long, TimeDiff As String
  StartTime = ActiveCell.Value
  EndTime = Application.InputBox("Select the cell with the ending time...", Type:=8)
  BreakTime = 60 'Not sure how you plane to calculate 30 or 60, but it goes here
  TimeDiff = Format(EndTime - StartTime - TimeSerial(0, BreakTime, 0), _
             "h"" hours and ""m"" minutes which excludes a " & BreakTime & " minute break.""")
  MsgBox "Start time: " & StartTime & vbLf & "End time: " & EndTime & vbLf & "Time difference: " & TimeDiff
End Sub

[Smd747]

Thank you Rick
Code2

Sub GetStartEndTime2()
  Dim StartTime As Date, EndTime As Date, BreakTime As Long, TimeDiff As String
  StartTime = ActiveCell.Value
  EndTime = Application.InputBox("Select the cell with the ending time...", Type:=8)
  BreakTime = 60 'Not sure how you plane to calculate 30 or 60, but it goes here
  TimeDiff = Format(EndTime - StartTime - TimeSerial(0, BreakTime, 0), _
             "h"" hours and ""m"" minutes which excludes a " & BreakTime & " minute break.""")
  MsgBox "Start time: " & StartTime & vbLf & "End time: " & EndTime & vbLf & "Time difference: " & TimeDiff
End Sub

works fine. It deducts the Break Time and displays the actual time worked. I am still studying your code to fully understand it and to learn from it. Thanks again for your help and direction.

[Smd747]

First hiccup with the new code. It does not account for overnight . When I click active cell 6:00 PM and ending cell 2:30 AM it give me 16 hours
Do I need to use the mod function??

[Rick Rothstein]

First hiccup with the new code. It does not account for overnight . When I click active cell 6:00 PM and ending cell 2:30 AM it give me 16 hours

Try it like this and let me know if it works for you or not...

Sub GetStartEndTime2()
  Dim StartTime As Date, EndTime As Date, BreakTime As Long, TimeDiff As String
  StartTime = ActiveCell.Value
  EndTime = Application.InputBox("Select the cell with the ending time...", Type:=8)
  BreakTime = 60 'Not sure how you plane to calculate 30 or 60, but it goes here
  TimeDiff = Format(24 + EndTime - StartTime - TimeSerial(0, BreakTime, 0), _
             "h"" hours and ""m"" minutes which excludes a " & BreakTime & " minute break.""")
  MsgBox "Start time: " & StartTime & vbLf & "End time: " & EndTime & vbLf & "Time difference: " & TimeDiff
End Sub

[Smd747]

Thanks Rick, that fixed the time calculation. It is working beautifully now, what a time saver. I have a ribbon button for 30 minute 1/2 hour break and another for 60 minutes 1-hour break

Thanks for the help and learning experience in VBA

[Rick Rothstein]

Thanks Rick, that fixed the time calculation. It is working beautifully now, what a time saver. I have a ribbon button for 30 minute 1/2 hour break and another for 60 minutes 1-hour break

Great!

Thanks for the help and learning experience in VBA

You are quite welcome... I am glad I was able to be of assistance to you.