Adding leading zeros to a calculated value
 

I have a formula that does exactly what its supposed to do (ie convert
latitudes in decimal format to latitudes in Degrees minute seconds format)
=ABS((TEXT((INT(ABS(C3))*10000),"00")+TEXT(INT(MOD (ABS(C3),1)*60),"00")*100+
INT(MOD(MOD(ABS(C3),1)*3600,60)))*SIGN(C3))& IF(C3<0,"S","N")

the problem is that I always want the degrees to have two digits even if it
is two 00 so that the cell always contains 123456N or 004530S

any suggestions please?


John J

Adding leading zeros to a calculated value
 

Why go for a big hairy formula when Bob's approach works fine

=TEXT(ABS(C3/24),"[hh]mmss")&IF(C30,"N","S")

The above will lead with zero

does not put in a negative sign

Regards,
Tom Ogilvy

John Johndon wrote in message
...
I have a formula that does exactly what its supposed to do (ie convert
latitudes in decimal format to latitudes in Degrees minute seconds format)

=ABS((TEXT((INT(ABS(C3))*10000),"00")+TEXT(INT(MOD (ABS(C3),1)*60),"00")*100+
INT(MOD(MOD(ABS(C3),1)*3600,60)))*SIGN(C3))& IF(C3<0,"S","N")

the problem is that I always want the degrees to have two digits even if
it
is two 00 so that the cell always contains 123456N or 004530S

any suggestions please?


John J

Adding leading zeros to a calculated value
 

Umm, well, I guess the big formula approach was one of the solutions and it
was the way I was initially thinking about it.....
I guess ismpler is better.....and it works....

Thanks

JJ

"Tom Ogilvy" wrote in message
...
Why go for a big hairy formula when Bob's approach works fine

=TEXT(ABS(C3/24),"[hh]mmss")&IF(C30,"N","S")

The above will lead with zero

does not put in a negative sign

Regards,
Tom Ogilvy

John Johndon wrote in message
...
I have a formula that does exactly what its supposed to do (ie convert
latitudes in decimal format to latitudes in Degrees minute seconds
format)


=ABS((TEXT((INT(ABS(C3))*10000),"00")+TEXT(INT(MOD (ABS(C3),1)*60),"00")*100+
INT(MOD(MOD(ABS(C3),1)*3600,60)))*SIGN(C3))& IF(C3<0,"S","N")

the problem is that I always want the degrees to have two digits even if
it
is two 00 so that the cell always contains 123456N or 004530S

any suggestions please?


John J