Date format remains when paste values?

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
326
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
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
326
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,119
Does a replace or substitute function around the vlookup before copy/paste do anything?
 

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
326
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,428
Messages
5,658,737
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top