Libre Excel Issue with concatenating "0"+ Value lesser than 10

[DaveLembke]

I am working in Lbre Excel and trying to prepend a 0 to values lesser than 10 so 1 would be 01 and all values 10 or greater would be without a prepended 0

I was thinking I could do this with an IF statement that would concatenate adding a 0 before a value if W3 is lesser than 10, yet if W3 is equal to or greater than 10 just display the double digit number without the added 0

I tried using this below by following guides on google for IF logic and Concatenation and I get a Err:508 in the cell

=IF((W3<10) THEN (U3&0&ROUND(W3)) ELSE (U3&ROUND(W3)))

Curious what I am doing wrong here 

I tested a concatenate function with =(U3&0&ROUND(W3)) and that works ... but need to have it working within an IF, THEN, ELSE to add 0 only if  W3 is lesser than 10.

[BC_Programmer]

Can't you just add leading zeroes in the cell format dialog?

What is U3?


or use the TEXT function:

Code: [Select]

=TEXT(ROUND(W3),"00")

Pretty sure THEN/ELSE are not part of the IF function, which is likely why that part doesn't work. Should be something like this (Though again I don't know what U3 is here?)

Code: [Select]

IF(W3<10,(U3&0&ROUND(W3),(U3&ROUND(W3)))

[DaveLembke]

Thanks BC for help with this... so the spreadsheet is taking government clock ring time in 100 units vs 60 units per hour where 15.25 is 15:15 ( 3:15pm) and the formula I am using uses modulus to strip the minutes from the whole hours. That works fine to break the hours and minutes apart. Next is conversion of the minutes from 100 to 60 and that part works fine. Next is concatenate the hours and minutes under normal 60 minutes per hour which works fine; however 0 through 9 require a leading 0 otherwise the time of 3:07 would show up as 3:7. If I didnt test for value to be lesser than 10 then the leading 0 would be for all and that would cause problems with time of say 3:15 showing up as 3:015, so it needs to be a conditional leading 0

The U3 is the Hours and the W3 is Minutes * Note in the formula I provided it doesnt show time with : as the time will be without the colon and instead as 3:15pm as 1515

I tried using the formula of =IF(W3<10,(U3&0&ROUND(W3),(U3&ROUND(W3))) and it gives me a Err:508 in the cell that is taking the formula that when correct should display a correct time.

[BC_Programmer]

There was a missing paren I think. Not sure how that happened since I was copy-pasting from excel

Code: [Select]

IF(W3<10,(U3&0&ROUND(W3)),(U3&ROUND(W3)))


[DaveLembke]

Thanks BC, that got me closer to to correct formula. It looks like it wants semi-colons instead of comma's to work properly. This below now does what I need it to.

=IF(W3<10;(U3&0&ROUND(W3));(U3&ROUND(W3))

Many Thanks Man