Hi,

I am working on an Excel table with data looking like this 100:03:00. I select the cell and it shows cell's value as 1/4/1900 4:03:00 AM.



I want the cell value to be shown as 100:03:00 as I will be using vlookup function to look for these in another table.

Thanks.

Aikeedoh, I'm not understanding something about your description. There are two ways the value of a cell is displayed in Excel. (Actually, someone else may chime in here and tell me there are more than two, but I can think of two just now). The ordinary way is in the cell itself; you use formatting strings to control that. And the other is what you see in the formula bar at the top when you select the cell.

Now, you say the data "looks like this 100:03:00", and I suppose you mean in the cell. And you say you want it "to be shown as 100:03:00", and again I suppose you mean in the cell. So if you want it to look the way it already looks, what's the problem? And yes, when I type 100:03:00 in a cell, it shows up as 1900-01-04 04:03:00 in my formula bar; that's normal.

Second, you're worried about how it will work with VLOOKUP. But unless I've misunderstood you, that isn't a problem; how it's formatted won't affect Excel's ability to find it with one of the lookup functions. You could display it as "100:03:00", "4.16875", "4.169E+00", "4 27/160" or anything else you can think of and Excel will still find it correctly with VLOOKUP.

Bob,

Sorry for not being clearer. Yes, the cell is displaying "100:03:00", but when I do a VLOOKUP from another sheet, the returned value is not "100:03:00", but instead it is returning a different value "4.16875". What can I do to have the returned value displayed as "100:03:00"?

Thanks

It would be easier if you posted a sample sheet

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Ah, I see. 4.16875 is actually the correct value, Aikeedoh; it's just not formatted the way you want.

Let's say that the worksheet where you're doing the VLOOKUP is named Master, and the worksheet it's looking at is Target. In Target it looks right ("100:03:00"), but in Master it's wrong ("4.16875")—or rather the value is right, but the format is wrong. In Master, change the formatting to "[h]:mm:ss", and 4.16875 will be displayed as "100:03:00".

Here's why: Excel stores dates and times as whole numbers. 4 is four days; 4.5 is four and a half days, ie 4 days and 12 hours. Just now the date and time where I am is 41507.66646, that is, 41507 days since 1900-01-01, plus .66646 of another day. That comes out to 2013-08-21 15:59:42.550, you see.

Now, what Excel is finding is 4.16875. If you tell Excel to format the result (in Master) as "yyyy-mm-dd", it'll ignore the time part and just display "1900-01-04" (four days since the start of 1900). If you format it as "hh:mm:ss", it'll ignore the whole days and show just the fraction of the day as "04:03:00". But put square brackets around the hours, "[h]:mm:ss", and it'll show it the way you want.

Your problem is that Excel converts anything that vaguely looks like a date INTO a date. Simply change the number format of this column to Text and you should be OK. Your value is not really a number, it is text composed of alphanumeric characters which happen to be mostly digits - but this is not the sort of number used in calculations.