Excel paste special values does not show value as displayed in the source cell

Question

I'm trying to copy and paste row values from a sheet to another using the paste special values option. My source cell is a date custom formatted to YYYYMMDD

                       Source Cell
Orig Value = 1/15/2018     Formatted value (YYYYMMDD) = 20180115

What I want is to copy the formatted value of the source cell and paste in in the destination cell

                      Desired Results
Formatted Source cell = 20180115    Destination cell = 20180115    

However, using the paste special - values results in a different amount in the destination cell

                      Actual Results
Formatted  Source cell = 20180115    Destination cell = 43115

Can the Excel guru's here guide me on how to make my desired results a reality.

P.S: The Excel i'm using is Excel 2013. Sorry for the terrible title : There should be no formatting done in the destination cell.

Answer 1

Have you formatted the destination cell - otherwise excel will paste the date code which is 43115.

Answer 2

You need to do the following.

1. Copy the Cell and move to the Destination.
2. Click Paste then hit Paste Special.
3. Select Value button and Ok.

Check the Screen Shot.

First I've copied the Original Date then in next Cell Paste it and applied the Format YYYYMMDD.

Then-after, first Copied Original Date and Pasted it as Value in 3 adjacent Cells, Formatted as General, Number and Short Date.

Repeated the same sequence for 20180127.

Note, as alternative you can use the DATEVALUE formula also, but always remember it only considers Date in MM/DD/YYYY format.

Hope this help you.