Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Outlook Date Text into Excel cell does not become date field

Posted on 2014-01-29
6
402 Views
Last Modified: 2014-02-03
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...;)
0
Comment
Question by:Michael Green
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39819831
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
 

Author Comment

by:Michael Green
ID: 39819842
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
 
LVL 23

Expert Comment

by:Danny Child
ID: 39820471
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 23

Accepted Solution

by:
Danny Child earned 500 total points
ID: 39820475
of course, need to set a Custom format on the cell with the formula:
dd/mm/yyyy hh:mm:ss
0
 

Author Comment

by:Michael Green
ID: 39828226
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
 
LVL 23

Expert Comment

by:Danny Child
ID: 39831118
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Calculation 4 46
Need Help with Mapping and Toggling 3 21
Turn date into age 17 32
Create an Excel User-form to Display Filter Results - Modify Macro 10 24
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question