Dates turn into 5-digit number

[Wiggle12]

Hi everyone,

I am having trouble with dates in excel 2010.
I have two workbooks A and B. I connect the two workbooks so that I can copy the data in workbook A to workbook B.
However, when I do this the dates show up as 5-digit numbers (when I click the cell it also shows a 5-digit number).

Does anyone have a solution for this?

Thanks in advance!

[zbor]

Dates are numbers in excel.

1 is 1.1.1900
2 is 2.1.1900
etc

You just need to format those numbers as a dates.

Right click -> Format cells

[owenaldridge]

Hi Wiggle12,

Try changing the cell format to date, it's most likely set to number format.

Thanks

Owen

[Wiggle12]

The cells are formatted as dates, that is the weird part about it..

[Special-K]

Examples?
Attach a sample spreadsheet, remove any sensitive data.

[Wiggle12]

Hi Special-K,

Attached a sample spreadsheet.Example.xlsm

[zbor]

Those are not numbers.
Those are text values.

Click F2 on cell (for edit) and enter.

[zbor]

Or select blank cell
Copy

Select D and E columns
Right click -> Paste Spetial
Add
OK

then format cells as Date

[Glenn Kennedy]

They're not really dates, but TEXT. Did you import the data from a CSV, or something? If so, check the way you did text to columns conversion. You can always coerce them into being recognised as dates by adding 0 to each one... = a22+0 will recognise the content of A22 as a number (and hence as a date if the cell is date-formatted).

[Wiggle12]

Thank you!! It works now

[Wiggle12]

I used the F2 enter function in the original document now, which serves as a template, and now it copies into other workbooks just fine! I have no idea how the data became this way, but it's fine now so that's all that matters. Thank you guys for your help and quick replies!

[Wiggle12]

I have one more question. I have gone through my entire document and used the F2 enter function to ensure that the dates are actual dates and not text. However, when I copy the text from workbook A to workbook B and the first rows are empty (because some options are not used), for some reason I get the 5-digit numbers again instead of the date. If I then use the F2 enter function again then it works, but is there a way to make sure that excel just gives me dates every time instead of the 5 numbers? Perhaps a macro or something?