Nested if statement question 2

[scriptscribe]

I have a group of data that I need to convert to hours minutes and seconds (with leading zeros) that looks like this.

124802
12458
2365
785
96
6

I've written the following code:

=IF(A1>99999,(CONCATENATE(LEFT(A1,2),":",MID(A1,3,2),":",RIGHT(A1,2))),IF(A1>9999,(CONCATENATE("0",LEFT(A1,1),":",MID(A1,2,2),":",RIGHT(A1,2)))))

This separates out the first two into
12:48:02
01:24:58

When I try to add a third nested if statement to convert the third and following cells into this time type I keep getting kicked back an error.

Any help would be greatly appreciated.

Mike

 

[scriptscribe]

Sorry, here's the rest of my code:

=IF(A2>99999,(CONCATENATE(LEFT(A2,2),":",MID(A2,3,2),":",RIGHT(A2,2))),(IF(A2>9999,(CONCATENATE("0",LEFT(A2,1),":",MID(A2,2,2),":",RIGHT(A2,2)), if (a>999, (concatenate("00:",left(a2,2),":",right(a2,2)), if (a2>99, concatenate("00:0", left(a2,1),":",right(a2,2)), if (a2>10, concatenate("00:00:", a2), if (a2<10, concatenate("00:00:0",a2)))))

 

[razman10]

scripscribe,

You are missing a closing parenthesis after your second "CONCATENATE".

You don't really need to use parenthesis in that form, however since you opened it, you must close it.

Here is the code you need:

=IF(A1>99999,CONCATENATE(LEFT(A1,2),":",MID(A1,3,2),":",RIGHT(A1,2)),IF(A1>9999,CONCATENATE("0",LEFT(A1,1),":",MID(A1,2,2),":",RIGHT(A1,2)),IF(A1>999,CONCATENATE("00:",LEFT(A1,2),":",RIGHT(A1,2)),IF(A1>99,CONCATENATE("00:0",LEFT(A1,1),":",RIGHT(A1,2)),IF(A1>10,CONCATENATE("00:00:",A1),IF(A1<10,CONCATENATE("00:00:0",A1)))))))

 

[onedtent]

now, that is a long formula.

how about this -
=IF(A2>99999,LEFT(A2,2)&":"&MID(A2,3,2)&":"&RIGHT(A2,2),IF(A2>9999,"0"&LEFT(A2,1)&":"&MID(A2,2,2)&":"&RIGHT(A2,2),IF(A2>999,"00:"&LEFT(A2,2)&":"&RIGHT(A2,2),IF(A2>99,"00:0"&LEFT(A2,1)&":"&RIGHT(A2,2),IF(A2>9,"00:00:"&A2,"00:00:0"&A2)))))

don't forget the answer will be text, not a number

it's a different ball-game if you want time as a number

 

[Zathras]

Your test data look a little odd with seconds values of 65 and 85, but here goes:

Try this (makes a string)

=MID(RIGHT("000000"&A2,6),1,2)&":"&MID(RIGHT("000000"&A2,6),3,2)&":"&MID(RIGHT("000000"&A2,6),5,2)

and if you want to "fix" the data where you have more than 59 minutes or seconds, or get the result as an actual time value (which you can then format) you can use this (makes a time value)

=TIMEVALUE(MID(RIGHT("000000"&A2,6),1,2)&":"&MID(RIGHT("000000"&A2,6),3,2)&":"&MID(RIGHT("000000"&A2,6),5,2))

 

[onedtent]

zathras, thanks

something new for me. so a *

 

[scriptscribe]

One from me also. That worked perfectly.

 

[Andrzejek]

What about some very simple Formating:

Call ShowTime("124802")
Call ShowTime("12458")
Call ShowTime("2365")
Call ShowTime("785")
Call ShowTime("96")
Call ShowTime("6")

Private Sub ShowTime(strStuff As String)

strStuff = Format$(strStuff, "00:00:00")

Debug.Print strStuff

End Sub

Debug.Print shows:

12:48:02
01:24:58
00:23:65
00:07:85
00:00:96
00:00:06

Have fun.

---- Andy

 

[mscallisto]

If you can make due without the colons you can use

=TEXT(a1,"00 00 00")

produces

12 48 02
01 24 58
00 23 65
00 07 85
00 00 96
00 00 06

 

[mscallisto]

Or if you really need the colons:

=SUBSTITUTE(TEXT(a1,"00 00 00")," ",":")

produces:

12:48:02
01:24:58
00:23:65
00:07:85
00:00:96
00:00:06

 

[mscallisto]

Or for simplicity

=TEXT(a1,"00\:00\:00")

produces

12:48:02
01:24:58
00:23:65
00:07:85
00:00:96
00:00:06