Equipment rolling run hours help

[seanb12491]

Hello. I'm fairly new to vba but I've managed to get a macro driven sheet together to help track run hours on a equipment category. Currently I have 3 columns assigned to a time format with the format as [hh]:mm. The 3 colums are start time, end time, and the difference.

What i am attempting to do is using vba copy the end time to a variable, then save the variable as the new start time.

But when I seems to stick at 24:00. Currently the cells show time as 9:00 PM and after the vba runs it shows time as 1/1/1900 00:00am

Any idea what I'm doing wrong with the formating

 

[PeteWright]

Hi, could you post your formulas or code that leads to this error?

Calculating times in Excel can be tricky and the results sometimes hard to predict.

 

[seanb12491]

I just used the copy command within excel rather than writing the value to a variable

 

[PeteWright]

You cannot assign a time to a variable, rather a date.
But if you need to store a time in a variable it has to be of a different format, a time value actually.
Besides, calculating time differences can lead to wrong results if, for example, the time period goes past midnight.
So you need to handle this too.
If you simply copy & paste the time from a Sheet to VBA it will be simply text or a so called "String".

Here's an example of what you could do instead:

MrExcel_2025-02.xlsm
	A	B	C
1	start time	end time	diff
2	9:00 AM	5:00 PM	8:00:00
3	9:00 PM	6:00 AM	9:00:00
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	= TimeDiff(A2,B2)

Public Function TimeDiff(a As Date, b As Date) As Date
   If (a < b) Then
      TimeDiff = b - a
   Else
      b = DateAdd("d", 1, b)
      TimeDiff = b - a
   End If
End Function

In order to work from within a Sheet the VBA code has to be placed in a module!