Negative time differences

[JonRowland]

Hi,

Sorry this appears to be have been spoken numberous times but I can't seem to find an answer that fits my problem.

I have two cells which contain date/time

L2 = 25/12/2010 12:00:01
M2 = 25/12/2010 13:00:01

I need to work out the difference between M2 and L2

so my vba for this is
<code>
Cells(Row, 15).Value = Cells(Row, 13) - Cells(Row, 12)
</code>

This is ok is time is positive for get ######### when negative. So how can I get it to show the negative as -1 ?

Jon

 

[VoG]

Maybe like this

If Cells(Row, 13).Value > Cells(Row, 12).Value Then
    Cells(Row, 15).Value = Cells(Row, 13) - Cells(Row, 12)
Else
    Cells(Row, 15).Value = -1
End If

 

[JonRowland]

Hi Vog,

Sorry I didn't explain this fully.

My dates/times will varied so could be anything between 0 and 12 & the negatives. It's time zone differences.

Jon

 

[shg]

You mean retain the value but display as -1? Format the cells beforehand as hh:mm;-1

 

[JonRowland]

No minus values can be anything but need to be display as -1 or -2 etc not as ##########.

So for example

L2 = 25/12/2010 12:00:01
M2 = 25/12/2010 13:00:01

The difference between M2 and L2 is -1 hour

L2 = 25/12/2010 09:00:01
M2 = 25/12/2010 13:00:01

The difference -4 hours

but could have
L2 = 25/12/2010 16:00:01
M2 = 25/12/2010 13:00:01

where the difference is +3 hours

Hopefully more clearer.
Jon

 

[mikerickson]

this will put the hour difference in column 15

Cells(Row, 15).Value = 24 * (Cells(Row, 13) - Cells(Row, 12))

With the OP code, its not the value that is showing the #######, it the number format of the cell.

 

[JonRowland]

mikerickson,

Thx for response I tried this but got some very strange results. However, found out it was a case of when to multiple by 24 so end result that works is

<code>
Cells(Row, 15).Value = (Cells(Row, 13) - Cells(Row, 12)) * 24
</code>


Thx to all who helped.

Jon