Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Timespan Starting With Active Cell Value Using Input Box Value

  1. #11
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Smd747 View Post
    How could I hard code 60 which is 60 minutes lunch break or 1-hour
    Code:
    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)...
    Code:
    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
    Code:
    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

  2. #12
    Junior Member
    Join Date
    May 2013
    Posts
    9
    Rep Power
    0
    Thank you Rick
    Code2
    Code:
    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.

  3. #13
    Junior Member
    Join Date
    May 2013
    Posts
    9
    Rep Power
    0
    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??

  4. #14
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Smd747 View Post
    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...
    Code:
    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

  5. #15
    Junior Member
    Join Date
    May 2013
    Posts
    9
    Rep Power
    0
    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

  6. #16
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Smd747 View Post
    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!

    Quote Originally Posted by Smd747 View Post
    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.

Similar Threads

  1. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  2. Replies: 8
    Last Post: 04-16-2013, 02:04 PM
  3. List of Dates In DropDown Starting From Today()
    By rich_cirillo in forum Excel Help
    Replies: 5
    Last Post: 04-10-2013, 05:58 PM
  4. How to Lock or Unlock row basis previous cell input?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 07-25-2012, 02:40 PM
  5. Sample VB programs for Starting VB
    By sumit.dutt in forum Excel Help
    Replies: 2
    Last Post: 12-27-2011, 07:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •