Hello all,
Ok I'm using a vlookup formula that is working great for what I want but I discovered that when I enter a date on my sheet the formula is not returning the actual date I entered but it is returning just numbers. I've tried to format the cell to (date) and it still wont work. Does anyone have a suggestion I can try to correct this?
thx in advance
mrggutz
Last edited by mrggutz; 10-19-2010 at 09:36 AM.
post a sample.formatting should work,
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
mrggutz,
Please attach your workbook: click on the New Post button, then scroll down and click on the Manage Attachments button.
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
The cell with the VLOOKUP needs formatting as a date, Excel dates are actually numbers
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I tried formatting the cell that has the vlookup formula to (Date) and still got only numbers.
Here is my formula
=VLOOKUP(DATA!$B$6,Info!$F$5:$EC$500,42,0)&""
workbook to big to attach...sry
why the &"" at the end? thats changing the date value to text so it wont format as date
Last edited by martindwilson; 10-19-2010 at 09:23 AM.
well looky there that did it, I had that at the end to help return a blank cell if nothing in cell rather then have 0 in all the cells that didn't have a returned value. But it works now thx I learned something new today.
thx again,
mrggutz
To get the blank instead of a 0 (if that's truly what you want) you can use:
=if(VLOOKUP(DATA!$B$6,Info!$F$5:$EC$500,42,0)=0,"",VLOOKUP(DATA!$B$6,Info!$F$5:$EC$500,42,0))
thx I'll give that a try.
thx again ya are the best
It's probably easier on calculation times to use the option to hide zeros rather than run the VLOOKUP twice
That's why I said "if that's truly what you want". ;-)
Yes but you didn't explain the alternativeOriginally Posted by Cutter
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks