Time format

liorsitkovsky

New Member
Joined
Sep 15, 2006
Messages
9
Hello all.

I have been getting allot of help with my problem and I am close to solving it now.
The last thing (I hope) I need help with is changing the format of my time in my database in order for my vlookup formula to work. I am trying to divide my database into 3 or 4 time intervals and I can make the formula work but only when I change the time format from:

09:51:07.887 to 9:51:07

I am able to do this but only manually line by line and not for the all column. There are over 13000 rows so you can probably understand my frustration. I tried formatting the cell/s but it won't change. I wouldn't mind leaving the time format as it is and using the same format in my array table for the vlookup formula but that doesn't work either.

Any help would be appreciated.

Lior
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
liorsitkovsky,

Can you report the actual cell format you are using when you look in Format Cells?

Dufus
 

liorsitkovsky

New Member
Joined
Sep 15, 2006
Messages
9
it's under the general category in "format cells"

Whe I try to change it wound remove the milliseconds.
But I wouldn't mind keeping it the way it is but Vlookup doesn't recognize it.

Lior
 

liorsitkovsky

New Member
Joined
Sep 15, 2006
Messages
9
I should add that I can change the cell format but it doesn’t change the way it is displayed and therefore not recognizable by vlookup.

Lior
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi Lior

First a remark: Vlookup as most of excel funtions don't care about formats. Format is a way to display a value that is convenient for humans to read.

If I understand your problem, vlookup expects a value just to the second. If that is so you must calculate a new time, equal to the same you have less the milliseconds.

With a time in A1, try in B1

=LEFT(TEXT(A17,"hh:mm:ss.000"),8)+0
Format B1 as hh:mm:ss

If you use in A1 your example 09:51:07.887, format B as hh:mm:ss.000 and you'll see 09:51:07.000, the new value has no milliseconds.

Please test it and if it doen't work post the vlookup formula with details and a working example with some data.

Hope this helps
PGC
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Yes, that's the problem. The information is not actually an Excel date yet. In order for the Excel to see it as an actual date, you need to convert to an actual date. Follow these steps.

First, format one cell in the range to the format you would like for an actual date. Example HH:MM:SS.000

Next, hit F2 while you are in the cell you just formatted and then hit Enter. This will convert the information into an actual Excel date formatted number. You should be able to use this date in your data analysis and such.

Next, copy this format to all 13000 dates.

Now find an empty cell and hit CTL+C on the keyboard to copy the cell. Select all 13000 cells you want reformatted. Select Edit Paste Special... from the menu and select the option Operation Add and click OK.

Does that convert them all to the proper dates in the proper format?
 

Forum statistics

Threads
1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top