Copying mails from outlook to excel results in different date formats

[karthirm90]

I'm trying to copy emails or just a single email at times from outlook and paste into excel but the date formats is different in each row which includes mails from below list. Have been asked to achieve this to calculate ageing of tickets assigned to team. How do I make today, yesterday and last week email to show the proper date (like its in 2 week's before) when copied from outlook to excel. I did search in the internet for solutions but nothing has resolved. I've never done tasks like this before and would suggest any tips or points to resolve the issue.

1. today
2. yesterday
3. last week
4. 2 week's before

Among the above,

only 2 weeks before email date format is correct

and the other one's looks like in below image. If you see here,

• Today's email is shown just with time alone.
• Yesterday's email with day and time
• Last week's email with day and date
• 2 week's before email with proper date

I have also tried the format painter option in excel. When its applied from 2 week's before to today's one - the output is shown as 1/1/1900 and for the other two it doesn't change (yellow color highlighted) like in below image. How do I proceed ? Please help me.

I even tried =DATEVALUE(TEXT()) function but it doesn't work and then the output is #VALUE!. I'm not very good in excel. Can someone assist or guide me, I've been trying this for the last 15 days .

 

[karthirm90]

Can someone please help me. dont know how to proceed.

 

[Colo]

Hello! Welcome to MrExcel.

I believe that the easier way is to change the settings for Outlook.

Sorry, since I don't have an English version of Outlook, so the following menu name might not be correct.

At Outlook Tab, click View > Current View > View Settings >Advanced View Settings Compact > Format Columns > Received

Then at Format, change from Best Fit to suit your needs.