Date format remains when paste values?

[Newport Count]

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]

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).

 

[Newport Count]

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]

Does a replace or substitute function around the vlookup before copy/paste do anything?

 

[Newport Count]

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.