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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
liorsitkovsky,

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

Dufus
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,218,743
Messages
6,144,225
Members
450,531
Latest member
avril18

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
Back
Top