Help with converting D1 to time value when it's negative

[Captain Sensible]

Ummm probably haven't explained to well in the title. The time before the off is sent to excel in the format 00:01:00 to cell (say) D1, after the race time has passed the figure is sent as -00:00:22 which excel seems to treat as text. Is there a way I can convert this figure back to a numerical value ? I've tried substituting the - out and a couple of other things but haven't managed it yet so thought best post on here rather than waste any more time


thanks

[Mitch]

Do you need to know how far past start time an event is, or just that it's past? (might be a stupid question but I'll ask anyway )

Are you wanting to do it with a worksheet formula or vba code?

[Captain Sensible]

Need it to carry on counting really , I was just hoping to remove the minus sign and let it count upwards as it'd be ok for my code.

formula or vbs is fine, at the moment it kicks off some vba code


If Cells(1, 4) < 0.0223 Then


but stops when it goes to -00:00:01 which as we know most raves never go off to time. If i could get it counting upards after 00:00:00 that'd suit me fine as i could log say 3 minutes before the off and 3 mins after. It justs kicks off some code matching with the sportinglife site that I don't need to kick off continually.

[GeorgeUK]

you could use =ABS(cell)

which returns the absolute value - without the sign

[Captain Sensible]

Thanks george, doesn't seem to work when i try it as the date is being sent to excel as text by BA and excel just throws up a value error

[GeorgeUK]

How about =DateValue(ABS(cell))

[GeorgeUK]

actually, i think that's backwards

convert text to date Datevalue(cell)
and use ABS on that to convert to absolute value

=ABS(Datevalue(cell))

[Captain Sensible]

Still threw up a value error George, I manage to sort it by scrabbling bits together

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D11,CHAR(34),""),"-",""))

Probably some nice easy option out there but hey it works

Thanks for the help George wouldn't have been wiser with the time/date value thing if you hadn't helped so it saved me time

[Captain Sensible]

Grrr bloody thing threw up an error when the value wasn't negative so had to revise it to

=IF(ISERROR(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D11,CHAR(34),""),"-",""))),D11,TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D11,CHAR(34),""),"-","")))

[Mitch]

You could use....

=IF(LEFT(D11,1)="-",TIMEVALUE(RIGHT(D11,8)),D11)

I think.....

[Mitch]

You could use....

=IF(LEFT(D11,1)="-",TIMEVALUE(RIGHT(D11,8 )),D11)

I think.....

[Mitch]

Posted again coz of the sunglasses smiley.

[Captain Sensible]

Thanks mitch, works and is much shorter than my efforts