Date format

[Guest]

When I copy a date format from outlook it copies into excel as such: "Fri
25/11/2005 09:00"
How can I get excel to recognise this as a date field and reformat into
"DD/MM/YYY"?

 

[Roger Govier]

Hi Daniel

Format>Cells>Number>Custom> DD/MM/YYYY

Regards

Roger Govier

 

[Guest]

Thanks Roger,
I've tried this before but it does not show the date in DD/MM/YYYY format. I
think it's because the day i.e. "Fri" and time "09:00" is shown in the
original. I need excel to ignore the day and date and just display
DD/MM/YYYY.
How do I get around this?

 

[Roger Govier]

Hi Daniel

Maybe =INT(A1) where A1 holds the date you want to strip the time off.

Regards

Roger Govier

 

[Guest]

No the INT doesn't work either.
I think that there is no way around it.
Thanks for looking anyway.
Daniel.

 

[Ron Rosenfeld]

When I copy a date format from outlook it copies into excel as such: "Fri
25/11/2005 09:00"
How can I get excel to recognise this as a date field and reformat into
"DD/MM/YYY"?

I am assuming your regional settings (Windows/Control Panel/Regional Settings)
use the DMY format. If not, another solution is available.

1. In an adjacent column, use the formula:

=--(MID(A1,5,10))

and format as dd/mm/yyyy

2. (And this will work with any regional setting:

a. Select your column of cells with the dates
b. Data/Text To Columns
Delimited
NEXT
Delimiters SPACE
Treat Consecutive delimiters as one SELECT
NEXT

Select columns 1 & 3 and select "Do Not import column (skip)"
Select column 2
Date DMY
FINISH

You may need to also custom format the result appropriately.


--ron

 

[Guest]

Thanks Ron,
My Regional setting have been correct all along.
Option 1 worked for me.
Another question if you don't mind:
How do I put dd-MMM-yyyy into a dd/mm/yyyy format?
For example: 29-NOV-2005 to 29/11/2005.
Thanks.

 

[Ron Rosenfeld]

Thanks Ron,
My Regional setting have been correct all along.
Option 1 worked for me.
Another question if you don't mind:
How do I put dd-MMM-yyyy into a dd/mm/yyyy format?
For example: 29-NOV-2005 to 29/11/2005.
Thanks.

I'm glad that worked.

As an aside, I would not refer to regional settings as correct or incorrect.
Rather, for solution 1 to work, they have to be the same as the format used in
the text string you are converting. Just semantics, I suppose.

Once you have the date in your cell, from the top menu bar select:

Format/Cells/Number/Custom Type: dd/mm/yyyy

If there is no change, then the "date" in your cell is TEXT rather than an
Excel date value. Excel date values are serial numbers that start with 1 for
1/1/1900 (or 0 for 1/1/1904 if using the 1904 date system).


--ron