Calculating the Difference between two times even if it crosses a day

[rob737]

Hi Forum

Hope all is well, I have a question regarding subtracting and displaying the difference between two times in minutes even if the time crosses a day.

Excel Version: 2010
OS Level: Windows 7
Programming Experience: Novice

Background:

I have a user form with several progress bars that time the duration of tasks, some run in series and some in Parallel.

Question:

What I am trying to do is as follows.

1. When my application starts I get the current time AppStartTime
2. As I call each sub I want to get CurrentTime = AppStartTime – Now displayed in minutes
3. Also if I started my application at say 11:45 PM and it went into the next day the elapsed time in minutes accounts for this.
4. Also how should I dimension the CurrentTime and AppStartTime (as double?)

I hope someone can get me started on this puzzle.

Many thanks

Regards
Rob

 

[RickXL]

Hi,

Always Dim dates, times or "datetimes" as Date.

As long as you record Now at every time you should not have any problem with a subtraction. The answer will also be a Date. That is, the whole number part will be in days from 1900 and the fractional part will be the fraction of a day after midnight.

Just format any results so that you can see the hours and minutes or whatever you want to see. That can be done using a worksheet format or by converting the value to a character string using the Format function in VBA.

 

[rob737]

Hi Rick

Many thanks for your help, I am making progress however I am still a bit confused about how to declare the times and format them.

If I declare a date and try to format it I get runtime error 13 type mismatch. I can only seem to get it to work if I declare it as a String.

When I try to subtract two times I get the old runtime error 13 type mismatch.

This is the little test program I wrote:

Sub timetracker()
' Dim AppstartTime As Date causes runtime error 13 type mismatch when I try to set AppstartTime
Dim AppstartTime As String ' makes AppstartTime = minute second e.g. 57:47

date_test = Now()

AppstartTime = Format(date_test, "n:ss")

MsgBox "Start Time =" & AppstartTime

timecalculator AppstartTime

End Sub
---------------------------------------------------------------------------------------------------------------------------------
Sub timecalculator(AppstartTime)

Dim CurrentTime As String ' again I seem to need to make this a string

date1_test = Now()

CurrentTime = Format(date1_test, "nn:ss")

MsgBox "Start Time =" & AppstartTime ' check my start time working OK

MsgBox "currentTime =" & CurrentTime ' check my new time working OK

diffTime = CurrentTime - AppstartTime ' runtime error 13 type mismatch when I try to set AppstartTime

diffTime = Format(date1_test, "nn:ss") ' don't get this far

MsgBox "time difference =" & diffTime ' don't get this far

End Sub

I note if I do

Dim date_test As Date

date_test = Now()

' AppstartTime = Format(date_test, "n:ss") ' comment out AppstartTime

' Change to

Range("A4") = Format(date_test, "nn:ss") ‘ This seems to work but does some weird thing depending on how the cell is formatted.


…… I will keep plugging away.

-----------------------------------------------------------------------------------------------

Ultimatly what I am trying to do is as follows

I have sereral sub's that are allowed different times to run e.g 1800 seconds, 300 seconds etc

I want to set a value in each sub e.g dim allowedTime as constant? allowedTime = 1800

So then each time i call the sub get the value of diffTime = CurrentTime - AppstartTime

And then calculate the % difference between the allowed time e.g. 1800 seconds and the difftime e.g 180 seconds

Thanks for your help once again.

Best Regards
Rob

 

[RickXL]

Hi,

You need to keep all your calculations in variables Dim'd as Date. Then when you want to see them they should be formatted or otherwise turned into something human readable. For instance:

Sub DateTime()

    Dim DateTime1 As Date
    Dim DateTime2 As Date
    Dim Diff As Date
    Dim Seconds As Long
    
    DateTime1 = Now
    Application.Wait (Now + TimeValue("00:00:10")) ' Wait for 10 seconds
    DateTime2 = Now
    
    Diff = DateTime2 - DateTime1        ' Calculate the difference
    
    Seconds = Diff * 86400 ' There are 86400 seconds in a day

    MsgBox Seconds

End Sub

The above code logs a start time;
It then waits for 10 seconds to simulate a macro running for 10 seconds;
It then calculates the difference (this is still in internal time units - days are whole numbers and times are the fractional part).
It then multiplies the difference by 86400, the number of seconds in a day and displays it.

Please note: The above macro has a delay of 10 seconds in it. So, if you try it out, it will look as if nothing is happening for 10 seconds!

 

[rob737]

Hi Rick

Thanks so much I have it working now. I found this dateDiff and it works great with your suggestion

dtDuration = DateDiff("s", CDate(AppstartTime), CDate(CurrenTime))

MsgBox " Duration = " & dtDuration
PDifF = (dtDuration / AllowableTime) * 100
MsgBox " Percentage Difference = " & PDifF


So I will now bumble on until I get stuck again

Thanks Again

 

[RickXL]

Hi,

No problem.

If you are happy then I am happy

However, I have had a bit more time today to look at your code. The way I would do it is like this:

Sub timetracker()

    Dim AppstartTime As Date

    AppstartTime = Now()
    MsgBox "Start Time = " & Format(AppstartTime, "n:ss")
    timecalculator AppstartTime

End Sub
Sub timecalculator(AppstartTime)

    Dim CurrentTime As Date
    
    CurrentTime = Now()
    MsgBox "Start Time =" & Format(AppstartTime, "n:ss")
    MsgBox "currentTime =" & Format(CurrentTime, "nn:ss")
    MsgBox "time difference =" & Format(CurrentTime - AppstartTime, "nn:ss")

End Sub

Note that strings are not used at all. Everything is kept in the internal Excel DateTime format (Dim as Date) until you need to show it to a human when it is formatted.

If you keep things in the internal format then Excel provides lots of functions and formatting options to help you out.


Regards,

 

[rob737]

Hi Rick

Great nice and tidy, even I understand it, with all your help and others (too numerous to mention) I now have my project up and running. Basically it is a series of progress bars around 60 in total, built from frames labels etc. there is heaps of examples on the internet. The app tracks a disaster recovery test.

The challenge I had was some of the required IT recoveries need to be done in series e.g. DNS changes. Once this phase is completed 7 phases (all with significantly differing times) start in parallel. The next challenge was of these 7 phases some of them finish late and some early and they in turn trigger anywhere from the next 4 - 11 phases, and so on.

So as you can see depending on what happens there is an enormous number of possible combinations. So each time I call one of the phases

I know how long it should take e.g. 8400 seconds
I know what time it started
I know what time it is now
So I calculate the difference in time as a percentage of the 8400 and it works fine.

Also I had some help with calling the subs so what I did was have a master loop

For j = 1 To 86400 ' and from here if a phase is active

I pass a value k 'l,m,n etc

In the sub I do k=k+1

Bit gludgy but it works ok.

I will now spend some time testing it as the estimated time to run is 4-8 hours so I have ratioed this down so I can test in a reasonable time frame.

One of the issues I have is in the 60 or so modules the code is almost identical.

I am not too sure but I think may be my next Excel adventure is a class library. I have read about it and it truly males absolutely no sense what so ever.

Thanks one again for all your help, I am sure you will see a post from me soon

Cheers
Rob

 

[RickXL]

Hi and thanks for the feedback. It sounds like quite a project.

I am not an expert on classes but I have to say I don't think they are for everything. Subroutines, as we used to call them, will get you quite a long way. Classes are good when you can match them to objects. For instance, the macro modules behind the worksheets are classes. They provide the same functionality behind every sheet (e.g. event processing) and automatically appear when a new sheet does.

I once did a dial or meter, for fun. As a subroutine it would have had lots of parameters which would have been cumbersome and if I had wanted more than one dial I would have struggled to move the pointers etc independently without a complete re-draw. If I had wanted to do it "for real", classes would have provided a solution.

Anyway, don't let me put you off and you know where we are if you need help.

Regards,