CODEWITHSUNDEEP
datetimeexceloutlook-2007vba
Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Date format issue in outlook while to excel-outlook automation

I have exported email mesages to excel by using a macro. By using Mid function I have found the date in the email, but it's not formated as a date.

For example Thursday, January 12, 2012 8:30 AM This date is text, not a formated date. How do I format that as a date?

Upvotes: 0

Views: 729

Answers (3)

JimmyPena
JimmyPena

Reputation: 8764

If you wanted a formula solution:

Assume the following is in cell A1:

Thursday, January 12, 2012 8:30 AM

The following formula will extract the date portion and format it as a date:

=DATEVALUE(MID(A1,FIND(",",A1)+1,LEN(A1)))

This will show 40920 in the cell. Format the cell as Date to display it as a mm/dd/yyyy date (or however you want to format it).

Upvotes: 0

Skytunnel
Skytunnel

Reputation: 1083

You'll need to take out "Thursday, " bit first. And then use CDate. This Should Work

DateStr = "Thursday, January 12, 2012 8:30 AM"
DateStr = Mid(DateStr, InStr(1, DateStr, ", ") + 1)
DateVal = CDate(DateStr)

Upvotes: 4

Tony Dallimore
Tony Dallimore

Reputation: 12403

Try:

Format(DateValue, "dddd, mmmm d, yyyy hh:mm AM/PM")

Upvotes: 0

Related Questions