How to add hours, minutes, seconds to Now time in Excel VBA with using Time End value?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
How to add hours, minutes, seconds to Now time in Excel VBA with using Time End value?

So I have the following code that looks like the attached image:
What I would rather have is instead of just saying it's 5 more minutes and 39 secs - is add it to the started time with the variable: dStartProcessTime.

I'd rather text box:  txtTimeEstimate say estimate:  7:50:13 p.m.  
How do I incorporate DateAdd to do this?

I got the following from this link:

https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time


Dim dStartTimeFirstFile As Double, dEndTimeFirstFile As Double, dEstimateTime As Double
Dim sFileName As String
Dim lTotal as Long

lTotal = 0
  For i = 0 To Me.ListBox_FilesFound.ListCount - 1
    If Me.ListBox_FilesFound.Selected(i) Then
      lTotal = lTotal + 1
    End If
  Next

'if first file.... record time it takes to start process.
If lCounter = 1 Then
  dStartTimeFirstFile = Timer()
  txtTimeStart.Visible = True
  txtTimeStart.Value = "started: " & Format(TimeValue(Now), "h:mm:ss")
End If
      
'do process for the one file.... however long.
For i = 0 To Me.ListBox_FilesFound.ListCount - 1
    'if selected only run the adjusting excel file code
    If Me.ListBox_FilesFound.Selected(i) Then
       sFileName = Me.ListBox_FilesFound.List(i, 0)
       Call Adjust_Selected_File
    End If

    If lCounter = 1 Then
        dEndTimeFirstFile = Timer()
        'now with start and end time - you can determine time it will take.
        'Format((Timer - StartTime) / 86400, "hh:mm:ss")
        
        sMinutesEstimate = Format(((dEndTimeFirstFile - dStartTimeFirstFile) * lTotal) / 86400, "h:mm:ss")
        txtTimeEstimate.Value = "estimate: " & sMinutesEstimate
        txtTimeEstimate.Visible = True
        
      End If

Next

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Dim dStartTimeFirstFile As Double, dEndTimeFirstFile As Double, dEstimateTime As Double
Dim dStartProcessTime As Double, Duration As Double
Dim sFileName As String
Dim lTotal As Long

dStartProcessTime = Now()

lTotal = 0
  For i = 0 To Me.ListBox_FilesFound.ListCount - 1
    If Me.ListBox_FilesFound.Selected(i) Then
      lTotal = lTotal + 1
    End If
  Next

'if first file.... record time it takes to start process.
If lCounter = 1 Then
  dStartTimeFirstFile = Timer()
  txtTimeStart.Visible = True
  txtTimeStart.Value = "started: " & Format(dStartProcessTime, "h:mm:ss")
End If
      
'do process for the one file.... however long.
For i = 0 To Me.ListBox_FilesFound.ListCount - 1
    'if selected only run the adjusting excel file code
    If Me.ListBox_FilesFound.Selected(i) Then
       sFileName = Me.ListBox_FilesFound.List(i, 0)
       Call Adjust_Selected_File
    End If

    If lCounter = 1 Then
        dEndTimeFirstFile = Timer()
        'now with start and end time - you can determine time it will take.
        'Format((Timer - StartTime) / 86400, "hh:mm:ss")
        
        Duration = ((dEndTimeFirstFile - dStartTimeFirstFile) * lTotal) / 86400
        sMinutesEstimate = Format(Duration, "h:mm:ss")
        txtTimeEstimate.Value = "estimate: " & Format(dStartProcessTime + Duration, "h:mm:ss AM/PM")
        txtTimeEstimate.Visible = True
        
      End If

Next

Open in new window

"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
To initially display the time in your textbox do this
 txtTimeEstimate = Format(Now, "Long Time")

To add time to it do this where you would substitute your variable (which holds number of seconds) for the 320
txtTimeEstimate.Text = DateAdd("s", 320, txtTimeEstimate)

Author

Commented:
Appreciate the response Martin. - Got me what I needed!

Good try, byundt, but two things 1. Duration wasn't declared - 2 even when I put it to variant, the time was way off as to the estimate..   thank you though.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That's certainly not how to do it. Never handle date/time as text, always as DateTime. No exceptions.
Thus, to add 5 minutes and 39 seconds:

MinutesAdd = 5
SecondsAdd = 39
Me!txtTimeEstimate.Value = Now + TimeSerial(0, MinutesAdd, SecondsAdd)

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
FWIW, Duration was declared as Double in my original code. And I copied the expression that estimated the number of seconds needed for all the processing for the duration, so I am not understanding why the time was way off.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Hard to tell with no sample data but, again, handle date and time as DateTime, not something else, indeed not text.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial