Format time in excel using VBA

[gjsala]

I’m having trouble with formatting a cell with a time stamp using VBA. What I’m doing is subtracting two cells for example:
Cell 1 (1:03:25AM) – Cell 2 (12:45:22PM)
What I’m after is the difference in time but when I subtract the two cells I get “######” which is a negative number and showing this in time doesn’t work. Any ideas?

Thanks for your help!

 

[SkipVought]

Hi,

DATE is also part of TIME. faq68-5827

Include the date part as well OR add 1 to cell 1 value.

BTW, If you check Excel help for ####, you'll get an answer as to WHY.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjsala]

Skip,
How could you get the time difference between the two cells listed above?

Thanks.

 

[SkipVought]

OK, please explain why you are subtracting the larger value from the smaller value, resulting in a negative value?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjsala]

Skip,
I am capturing time stamps then I’m subtracting the two cells to get the difference in time. My example is one item finishes its process then the next item finishes I want to know how long it takes from item one to item two to finish. In my previous example if item 2 finishes at 1am and item 1 finishes at 12pm then I get a ## number for the difference between cells.

Thanks.

 

[osx99]

It may be easier to create a full timestamp with date and time. Eg

A1 = 31/10/2009 01:36:00
A2 = 30/10/2009 22:36:00
=A1-A2
= 00/01/1900 03:00

Alternatively, if your timestamps are all less than a full 24 hours apart you could use

A1 = 1:03:25AM
A2 = 12:45:22PM

=IF(A1<A2,A2-A1,A1-A2)
= 11:41:57 AM

 

[osx99]

My mistake I meant....

A1 = 31/10/2009 01:36:00
A2 = 30/10/2009 22:36:00
=A1-A2
= 00/01/1900 03:00

Alternatively, if your timestamps are all less than a full 24 hours apart you could use

A1 = 1:03:25AM
A2 = 12:45:22PM
=IF(A1<A2,A2-A1,TIME(23,59,59)-A2+A1)
= 11:41:57 AM

 

[SkipVought]

You must subtract the SMALLER value from the LARGER value in order to get a positive value.

You could always use the ABS function to get the absolute value of the difference if you want to be sloppy about it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}