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.

So to get cell G2 in correct format I have been doing the following:

Z2=("00"&G2)*1
Format Change: Custom hh:mm:ss
Copy
Paste Special, number and format into cell G2

Unfortunately its hard to automate this process as the rows of data are constantly changing with new associates. So basically what Im asking is how I can combine Formula 1 with Formula 2 in one formula.

Thank you for your time.

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

Sorry for the delay I had posted this on a Friday and just got in on Monday. Let me give it a shot. Thank you.

The second one worked to perfection in cell G2. I am forever grateful sir, thank you.

P108 works


P109 Does Not Work


As you can see.
P109 does not work nor do the following 59 rows up to P158. Consequently P109-V109 do not work all the way down to P158-V158 do not work and come up #N/A

Can anyone help? Is the problem is that there is a limit to how long the VLookup can be? can this be fixed?

Attatched are pics of the P108 cell and P109 cell and their formulas.

Maybe I could use a different approach if anyone could help. Instead of putting the ("00"&N2)*1 in the VLOOKUP formula, perhaps I couuld put it in when I macro format the columns to Custom hh:mm:ss.

Is there a way I could add ("00")*1 to all the cells along with this VBA code?

Range("AF8,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V").Select
Range("V1").Activate
Selection.NumberFormat = "hh:mm:ss"

Or is this going to be easier to just do in the excel sheet formula in each cell?

We made the range longer and it worked

Claus: Try (The range is longer – it now goes to row 1000):
=IFERROR(IF(ISNUMBER(FIND(":",VLOOKUP(N2,'Avaya Data'!$B$1:$N$1000,3,0))),("00"&VLOOKUP(N2,'Avaya Data'!$B$1:$N$1000,3,0))*1,VLOOKUP(N2,'Avaya Data'!$B$1:$N$1000,3,0)),0)

Thanks Claus u da man. Case solved. Now if we could just find out who shot Biggie and Tupac :-/