Date Questions
First, the easy one, the second: To translate these text values dates
INSIDE the cell:
Copy a black cell. Select the "date" data, EditPaste Special... and
choose Add. Subsequently format to some Date format.
To perform the same task in a different column
=DATEVALUE(A1)
For the first task the problem is with a number like 11107. Is it Jan
11 or Nov 1? If you are guaranteed that month will always be two
digits (e.g. April 1 2006 will appear as 10406) then you can use:
=DATE(2000+RIGHT(A1,2),--MID(A1,LEN(A1)-3,2),--LEFT(A1,LEN(A1)-4))
Does this help?
Kostis Vezerides
On Nov 23, 11:09 am, Patrick Bateman
wrote:
Hi all,
I have two problems with the format of two different columns of dates i have
coppied onto an excel sheet. I need to be able to have the dates in a
standard date format in order to be able to work with them. The problems are
as follows:
1)The first column of dates are written as numbers with no dividers, eg.
05/10/07 is written as 51007. Is there any easy way to just format this as a
date? Or will i just have to use a formula to convert it?
2)The second column of dates look like they're in date format - eg.
26/10/2007 but dont seem to be in a numerical date format as i am unable to
change the format of how the date is displayed or change it to a number??
any ideas would be much appreciated
thankyou
patrick
|