Dates become numbers

Karthik-Excelsior

Active Member
Joined
Mar 4, 2011
Messages
313
Hi excel gurus,

Have you ever experienced this - you copy 'Dates' from one Excel sheet and paste them on to another, and the Dates become Numbers.

Sometimes, even after selecting them and choosing 'Currency' format doesn't help. Then, I mostly use the TEXT formula to get the date format.

Any idea, why this conversion happens, and how this can be averted?

Thanks,

Karthik
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Serial dates in Excel essentially are numbers starting with 1 being 1 Jan 1900 and incrementing from that point. If you format a date such as 18 Jul 2012 as General it will display as 41108. If you convert them to text you will struggle with doing calculations based on the dates.

Dom
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Dates are stored as numbers in Excel, formatting makes them appear as dates.

So what's probably happened is, somehow, the formatting has been lost.

I don't know why you can't re-format the numbers to dates.

Have you copied to cells formatted as text?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,965
Members
414,114
Latest member
Lost_User21

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