Excel Date Problem

[marcus2704]

A client has a spreadsheet which was sent from the US, and therefore a
column of date entries are in the format of US type ie:

02/30/05 ie 30th feb 2005

I want these to be changed to the UK version ie:

30/02/05

However as regional settings are set to UK, Excel does not recognize
02/30/05 as a date in the first place, and does not convert it to a UK
format accordingly. Also, despite formatting the column for date it
still does not work.

I tried switching to a US locale, hoping that Excel would then
recognize 02/30/05 as a date, and then back to UK again but this did
not work.

He has several thousand entried in this format so its too much to
change manually.

Any ideas?

 

[HiArt]

Hi,

excel holds dates as the number of days since 1 Jan 1900 and times as
fraction of the day. So 3 Oct 2005 at 10:49 is held as 38628.45.

How does this help?

Firstly you need to be sure these are dates. Put the cursor over one o
the dates and press Ctrl + 1. You should get the "Format Cells" form.

Now under "Category" select either General or Number, but do not pres
OK. Do you see something which looks like the above example; e.g
38628.45102? If you see something like "3/10" your out of luck for
simple fix.

If you see 38628.543025 or similar, then select "Custom" unde
category. In "Type" clear whatever is shown there and add "dd/mm/yy"
Look at the sample, is this what you are after? Then press OK, selec
all dates and repeat (actualy 'dd/mm/yy' will now be selectable unde
"type"). Note you can alter formats to dd-mmm-yyyy for example to ge
03-Oct-2005.

If you still saw "3/10" then you need to create a new column. Put i
the following forumla: "=DATEVALUE(cell_reference)", e.g
=DATEVALUE(C2) and format the result as above.

HTH

Ar

 

[Harlan Grove]

A client has a spreadsheet which was sent from the US, and therefore a
column of date entries are in the format of US type ie:

02/30/05 ie 30th feb 2005

I want these to be changed to the UK version ie:

30/02/05

However as regional settings are set to UK, Excel does not recognize
02/30/05 as a date in the first place, and does not convert it to a UK
format accordingly. Also, despite formatting the column for date it
still does not work.

....

A support professional should know that data in Excel cells is either
numeric, text, boolean (TRUE/FALSE) or error. If it's not numeric, boolean
or error, it must be text. No matter how often you change the number format
of text cells, those cells' contents remain text, so are unaffected by
number formatting.

Change to US settings, then open the Excel workbook, select a blank cell,
run the Edit > Copy menu command, then select the cells containing dates,
run the Edit > Paste Special menu command, choose Values Add, and click OK.
Then save the file.

Note: 30-Feb-<whatever> will produce errors no matter how it's formatted.

 

[Dave Peterson]

But there is no 30th of February of any year. So no matter what your settings,
excel will never see that as a date.

And if you got a real .xls file from your client, then dates should have been
come in ok. All you may have to do is reformat them to dmy. That changes
what's displayed on the worksheet--not the underlying value of the cell--the
date stays the same.

On the other hand, if you're receiving a .CSV file (plain text, comma separated
values), then you could rename that .csv file to .txt and use File|Open to open
it.

You'll see a data import wizard pop up. You can make sure you choose the
correct format (mdy) for the date field. After the data has been imported, you
can change the format of the column to anything you want including dmy.

 

[marcus2704]

Hi thanks for the reply.

I see it as 3/10 so using the above as an example, in cell C2 I have
(for example) the US date 01/30/05. I create a new column beside this
and in C3 I enter -

=DATEVALUE(C2)

however Excel throws a error at me after this. Can you clarify to me
what Ive done wrong?

Thanks in advance.

 

[Sandy Mann]

Marcus,

XL is now seeing the dates as text. Unwieldy but try:

=DATE(2000+RIGHT(H1,2),LEFT(H1,FIND("/",H1)-1),MID(H1,FIND("/",H1)+1,FIND("/",MID(H1,FIND("/",H1)+1,255))-1))

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[marcus2704]

Harlan, that worked for me, thanks.