Solved

Outlook Date Text into Excel cell does not become date field

Posted on 2014-01-29
6
373 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:SGSMikeG
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Steven Harris
Comment Utility
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:SGSMikeG
Comment Utility
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:DanCh99
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 23

Accepted Solution

by:
DanCh99 earned 500 total points
Comment Utility
of course, need to set a Custom format on the cell with the formula:
dd/mm/yyyy hh:mm:ss
0
 

Author Comment

by:SGSMikeG
Comment Utility
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:DanCh99
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now