Hi,

Am Fri, 29 Mar 2013 23:15:10 +0000 schrieb jtfranco:

Please excuse the long subject title, I coulnd't word the topic any
other way.

I'd like to combine these two formulas

1. G2=VLOOKUP(L2,'Avaya Data'!$B:$O,3,FALSE)
2. Z2=("00"&G2)*1

Im importing call data in General format, which comes in such as
".80138" and sometimes it comes in ":18".
You can change ".80138" to custom hh:mm:ss which changes it to 19:14:00
but it doesn't change the ":18" to a uniform 00:00:00 format.

try in Z2:
=IF(ISNUMBER(FIND(":",G2)),("00"&G2)*1,G2)
or without helper column G:
=IF(ISNUMBER(FIND(":",VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))),("00"&VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))*1,VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2