Bring only date from time/date field in Excel 2007

[ComputerCharmer]

I am using vlookup to bring the contents of a cell into a different worksheet. I only need the date from a time/date field. Since time is expressed as a decimal, can I accomplish this by using INT in my formula? Thank you!

 

[Desu Nota from Columbus]

Have you tried formatting the cell where the vlookup returns the value?

A______B
1-----2/2/2011 12:00:00
2-----3/3/2011 12:00:00



C
=vlookup(1,A1:B2,2,0)

formatted as date c1=2/2/2011

Formatted as time c1=12:00:00




Will this work for you?

 

[ComputerCharmer]

That didn't work. Thank you for your assistance!

 

[Desu Nota from Columbus]

Is the date/time value given an excel serial number or is it text?

When you return the value with the vlookup, what do you see?

 

[baitmaster]

yes, use =INT(VLOOKUP(...))

You may need additional error handling for when the value returned is non-numeric, in XL07 or later you can put =IFERROR(... ,0) around it, else you will need IF(ISERROR(....) , 0, ....)

 

[ComputerCharmer]

I have imported a query that I created in Access into an Excel spreadsheet. I can format that cell to appear in the desired manner. My problem is occuring in my VLOOKUP on a different spreadsheet. The formula is pulling the time and date. And as you know, the cell that contains my formula cannot be formatted as a date. I am thinking that I either need to add another conditional formatting rule or adjust my formula. Do you Agree or Disagree with my logic?

 

[baitmaster]

so is it returning a value or a text string?

If its the latter, try adjusting your formula to =(....)+0 to force it to return a value

 

[ComputerCharmer]

Baitmaster, I think that worked! I was missing the error handling. I will add some bogus info to test it. THANK YOU, EVERYONE!!

 

[Desu Nota from Columbus]

welcome to the forum, it is a lifesaver.