Date Format - Time Zone issue?

ala5656

New Member
Joined
Jan 8, 2014
Messages
8
I received an excel file from a coworker in Texas, while I reside on the east coast. I thought nothing of it, until i was putting a series of dates in to it from another spreadsheet i had created previously. I realized that instead of the date displaying as "DD/MM/YY" in the excel file I was sent, the date was now displaying as "MM/DD/YY 7:00:00". In addition, all the dates were now off by -1 day i.e. a date that originally was 11/17/2008 was now displaying as 11/16/2008 7:00:00.

When I clicked on formatting, it said that that was an asterisk'd date. That has never meant anything to me, but i guess its worth noting. I tried everything to reformat the date so it a) displayed the way i wanted it to (DD/MM/YY) and b) it corrected the error of being behind one day. absolutely nothing worked.

i got so frustrated with everything I closed all the files and decided I would get a new original file with the original data and start fresh. I ran this report from a cloud based system, which i've done thousands of times, and exported to excel...only to now discover that no matter what the data looks like prior to my downloading it into excel, the dates will now only display in the "MM/DD/YY 7:00:00" format.

I've never experienced this bizarre issue, and while i was able to use a ROUNDUP formula to eventually correct the data, the point is, i shouldn't have to. Up until I received the file from my co-worker, i've had zero issues with downloading reports into excel and the dates not appearing as they should. I may be entirely off base, but the only thing i can think of is that somehow the file i received from my coworker messed up my settings somewhere.

if anyone has any insight or solutions, you would be my hero!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).


thank you for the reply and the link. The problem that I'm still having and most concerned about is that while the suggestions about using the VBA code in the file to format the dates does work; it doesn't address or fix the problem of the initial excel file now downloading & displaying the dates incorrectly. It would be one thing if i knew that only files that my colleague sent to me would be "different", but I could continue to run and download reports as usual like i always have. The problem is that ever since I received this file from my co-worker, every time I run a report with dates in them from my cloud based system, the dates are displaying incorrectly. Just wish there was a way to like...revert the settings somehow. This is going to affect all the work i do on a daily basis if i have to fix every report i run.
 
Upvote 0
Did you check your OS settings? They shouldn't have changed because of a file receipt but just to be sure.....

You can also ask the person who sent it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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