Copying mails from outlook to excel results in different date formats

karthirm90

New Member
Joined
Aug 8, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
1659938032624.png


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.

1659938140955.png


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 .
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top