0

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.

2

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

  • Thank you for the reply. I'm sorry but I failed to mention that the destination cell could not contain any formatting so I really need to have the pasted value already appear in YYYYMMDD format instead of "43115" date code. – ides Jan 28 '18 at 15:23
1

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.

enter image description here

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.

  • Thank you for the reply. I'm sorry but I failed to mention that the destination cell could not contain any formatting so I really need to have the pasted value already appear in YYYYMMDD format instead of "43127" value. – ides Jan 28 '18 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.