Time format

[liorsitkovsky]

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

 

[Dufus]

liorsitkovsky,

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

Dufus

 

[liorsitkovsky]

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]

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]

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]

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?

 

[liorsitkovsky]

Thank you. it worked

Lior