Outlook Date Text into Excel cell does not become date field

I was hoping someone can tell me why when I copy a date from Outlook's email header and paste it into an Excel worksheet cell that Excel is not smart enough to convert it to a date time field.

e.g.
Tue 28/01/2014 3:21 PM

N.B. Field above is in non-US / rest of the world format...;)
Michael GreenSenior IT Consultant / IT Project ManagerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Danny ChildConnect With a Mentor IT ManagerCommented:
of course, need to set a Custom format on the cell with the formula:
dd/mm/yyyy hh:mm:ss
0
 
Steven HarrisPresidentCommented:
Excel is not smart enough to convert it to a date time field.

"Tue 28/01/2014 3:21 PM" is not a standard date format for Excel.  While Excel is not sure how to process your information without further input, such as cell formatting, I would say that Excel is far from "stupid".
0
 
Michael GreenSenior IT Consultant / IT Project ManagerAuthor Commented:
I'm afaid I don't understand your response.  What information is missing that would stop Excel from being able to process this date and time string ?
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
Danny ChildIT ManagerCommented:
The problem here is that Outlook is trying to be helpful by giving the day Name (Tue), which is unnecessary info as far as Excel is concerned.

The easiest way is to strip this out
=VALUE(RIGHT(B3,LEN(B3)-4))

This basically converts the date into a numerical value by ignoring the first 4 characters.
I've used the LEN function to accommodate the possiblity that the times and dates may have different lengths eg
28/01/2014 3:21 PM - is 18 characters long, but you could also have
28/01/2014 12:21 PM -  at 19 characters, or
2/01/2014 3:21 PM - 17 characters - but I think Outlook may pad this one out with a leading 0...
0
 
Michael GreenSenior IT Consultant / IT Project ManagerAuthor Commented:
Great solution / work-around Dan.  However I'm still puzzled as to why Microsoft wouldn't make it so that Excel can handle an Outlook date text without this extra effort.
0
 
Danny ChildIT ManagerCommented:
in fairness, in 20 years in IT Support, I've never been asked this before... But that's one of the reasons I like EE, to find new questions!  I'd be interested to hear the context of the question if you're bored...

But the real reason for the problem, is that Office has its own product teams, doing things their own way, and it's like that, that's the way it is....

My favourite clash is that CTRL+F in most Office apps is Find,  but in Outlook it's Forward...!  Outlook uses CTRL+E (in some views, at least...)
0
All Courses

From novice to tech pro — start learning today.