Date format remains when paste values?

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
324
Hi there,

I have a VLOOKUP formula that pulls through a date (dd/mm/yyyy) but the format converts the appearance to dd.mm.yyyy.

I notice that when i copy and paste values, the dd.mm.yyyy value is present, but it remains as dd/mm/yyyy in the formula bar!


How do i keep this as dd.mm.yyyy? I need the data in this format to then upload correctly!


Sounds simple but everything I have tried results in the same issue.


Cheers,
Rich
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
Vlookup returns only the contents of the cell looked up. It can be the # of days since 1/1/1900 or a text string (especially if imported from a database). The cell's format determines how the number actually appears. You can convert a text date to a value with ctrl-h replace the delimiter with itself or highlight and run a line of VBA code (Alt F11 - ctrl-G selection.value = selection.value).
 
Last edited:

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
324
Sure i was using Ctrl+H to replace '/' with '.', but wondered if there was another way e.g. a paste special that would do it.

Thanks for looking! :)
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
Does a replace or substitute function around the vlookup before copy/paste do anything?
 

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
324
Tried that but it then reverts back to the number format e.g. 43217.

It can work with a copy, paste value and then replace function, not to worry.
 

Forum statistics

Threads
1,081,798
Messages
5,361,352
Members
400,628
Latest member
teresajm

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top