MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting data in Excel

Posted by Winston Glass on February 02, 2002 9:55 PM

I am a beginner with creating Macros in Excell. How do I format data, not the cell itself. The data is in time format. The first piece of data is extracted from a raw file and looks like this. 8:50 Its value is 8 hrs and 50 minutes. To be correct it needs a :00 behind it in the cell. 8:50:00 The other data's value is 2:19 Its value is 2 minutes and 19 seconds. It needs a 0: in front of it in the cell. 0:2:19 The data is in two different colums
Please help

Posted by Jac in the UK on February 03, 2002 10:23 AM

Hi Winston--

Selection.NumberFormat = "h:mm:ss"

Will convert the data OK, bit lost as to why convert the dat and not the cell, i would just go for it and get the job done.

Can i ask why?

Posted by Winston Glass on February 03, 2002 1:48 PM


Thanks for your response. I has tried the info you sent and you are correct. It does provide the same info. The reason I need it is because I report individual stats for a call center with 234
individual techs. Supervisors look at my reports
and unless the zero's are in the right place there is no way for them to tell if a tech has been on the phone for 8 hrs 50 minutes, 8:50 or 8 minutes 50 seconds. 8:50 so I have been formatting the column of cells and adding the :and zeros manually. Hope this helps.


Posted by Jack in the UK on February 03, 2002 2:31 PM

Hi again Winston--
I understans my ways perfect in the real word but not in your, thtas excel for you never real your or mine.

Lokk i hav some codes ill look over i still say this the info i gave is dead on what inned to do is run that on a cloumn to crunch it to your requirements.

Fell i might sus that one - stay on this feed might take me 48 hrs sadly i do two job at the same time no extra money just hard work, excel most and how i moan and complaign... not!

Stay posted..