Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONVERT NOs TO EAN 8 BY ADDING LEADING ZEROS | Excel Discussion (Misc queries) | |||
leading zeros | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
Adding Leading Zeros to Text | Excel Discussion (Misc queries) |