John,

You don't need VBA, you can do this simply with worksheet formulae.

In essence, what you do is to force it into a pseudo-time format and display
it that way. Let's assume that the data is in column A, and we will start
with A1, and put the result in B1. As Excel holds time as a fraction of 1
day, you start by dividing the value in A1 by 24 to convert to a fraction of
1 day, and because Excel 1900 date system cannot handle negative dates, we
will take the absolute value
=ABS(A1/24)
You could then format this with the custom format "[h].mm.ss" and get the
answer of 26.07.24, but that does not differentiate between N and S and you
want more so we won't do that, we will continue.

Next we will add a N/S indicator. To do that we will just add a test whether
A1 is negative or not and string a value at the end.
=ABS(A1/24) & IF(A1<0,"S","N")
Pretty good, but as we have general format on the cell, it comes out like
1.088475N which is not what you want.

So we now have to format the pseudo-time part bit, and we can do that in the
formula by using the TEXT function
=TEXT(ABS(A1/24),"[h].mm.ss")
which gives us the same result as above if we had used custom cell formats.

Put it all together, and we finally have in B1
=TEXT(ABS(A1/24),"[h].mm.ss") & IF(A1<0,"S","N")
and I think that this is exactly what you want, so just copy it down column
B.

So we now have to format the pseudo-time part bit, and we can do that in
the
formula by using the TEXT function
=TEXT(ABS(A1/24),"[h].mm.ss")
which gives us the same result as above if we had used custom cell
formats.

Put it all together, and we finally have in B1
=TEXT(ABS(A1/24),"[h].mm.ss") & IF(A1<0,"S","N")
and I think that this is exactly what you want, so just copy it down
column


Bob,

Can you explain the format "[h].mm.ss"............I don't understand the
square brackets around the "h". When 26.1234 is in A1 and I remove the
brackets from the formula it displays a 2 instead of 26. I don't understand
and can't find documentation on that type of format. Can you explain?
Regards. -Guy

time is stored as the elapsed number of days from a base date. So the whole
number is the number of days and the fractional portion is the number of
hours. By dividing by 24, this converts the 26.1234 hours to a date serial
number. When displaying a date serial number with a date format, Excel
will see the serial as this elapsed time, so 26.1234 hours would be 1 day
and two hours. To get Excel not to "extract" the full days / 24 hours
periods, you can put brackets around the format for the hour portion - then
it will show 26. You can also do minutes [mm]:ss

and then the total number of minute would be shown rather than extracting
out the hours.

Regards,
Tom Ogilvy


Guy LaRochelle wrote in message
...

So we now have to format the pseudo-time part bit, and we can do that in
the
formula by using the TEXT function
=TEXT(ABS(A1/24),"[h].mm.ss")
which gives us the same result as above if we had used custom cell
formats.

Put it all together, and we finally have in B1
=TEXT(ABS(A1/24),"[h].mm.ss") & IF(A1<0,"S","N")
and I think that this is exactly what you want, so just copy it down
column


Bob,

Can you explain the format "[h].mm.ss"............I don't understand the
square brackets around the "h". When 26.1234 is in A1 and I remove the
brackets from the formula it displays a 2 instead of 26. I don't
understand
and can't find documentation on that type of format. Can you explain?
Regards. -Guy

Tom,

thanks for taking the time to explain that one, cause it had me really
confused.

JJ


"Tom Ogilvy" wrote in message
...
time is stored as the elapsed number of days from a base date. So the
whole
number is the number of days and the fractional portion is the number of
hours. By dividing by 24, this converts the 26.1234 hours to a date
serial
number. When displaying a date serial number with a date format, Excel
will see the serial as this elapsed time, so 26.1234 hours would be 1 day
and two hours. To get Excel not to "extract" the full days / 24 hours
periods, you can put brackets around the format for the hour portion -
then
it will show 26. You can also do minutes [mm]:ss

and then the total number of minute would be shown rather than extracting
out the hours.

Regards,
Tom Ogilvy


Guy LaRochelle wrote in message
...

So we now have to format the pseudo-time part bit, and we can do that
in
the
formula by using the TEXT function
=TEXT(ABS(A1/24),"[h].mm.ss")
which gives us the same result as above if we had used custom cell
formats.

Put it all together, and we finally have in B1
=TEXT(ABS(A1/24),"[h].mm.ss") & IF(A1<0,"S","N")
and I think that this is exactly what you want, so just copy it down
column


Bob,

Can you explain the format "[h].mm.ss"............I don't understand
the
square brackets around the "h". When 26.1234 is in A1 and I remove the
brackets from the formula it displays a 2 instead of 26. I don't
understand
and can't find documentation on that type of format. Can you explain?
Regards. -Guy