date and graphing

[scavia]

I actually have 3 related problems.

The first problem is that the sheet I was given has time stamps that are right justified and easily read as an actual date but at some point in the column the format changes to left justified and it is not read as a date.

The second problem is I want to convert these dd-mm-yyy hh:mm formats to mm-dd-yyy hh:mm

The third problem is when I plot data verses these dates, the x-axis starts at 1/0/1900 and not the actual date.

[MrShorty]

It looks to me like it is all the same problem -- converting date/times stored as text to real date/time serial numbers.

Starting with the third problem, any time an Excel scatter chart (assuming you intend to use a scatter chart) encounters text (even numbers/dates stored as text) in the x axis range, Excel will default to simple count numbers (1,2,3,...) for the x axis values. When formatted as dates, of course, those serial numbers represent dates in January of 1900. This problem will resolve itself once you get the text converted to numbers.

The second problem is similar. Excel's number formatting has no impact on text. Once you get the dates converted from text to number, you will have no trouble applying whatever number/date format you want.

The first problem seems to be a mismatch between your operating system's settings, which Excel uses for recognizing dates when they are text, and the text you are importing/entering into Excel. The dates that are still text are in a dd/mm/yyyy h:mm format and appear to represent date/times on 13 Jan 2016. The dates that have been converted to numbers were interpreted as 1 Dec 2016 dates, but I would guess that they were intended to be 12 Jan 2016 dates. Considering your general location, I expect that your operating system is set to recognize and prefer MDY dates, so Excel misinterpreted 12/01... dates as December dates, and could not recognize 13/01... dates because there is no such thing as as 13th month.

How are you entering/importing these data into Excel? If this were my project, I would do what I could to enter/import the data into Excel in a way that allows Excel to read the data correctly. Obviously, we cannot comment on that unless we know where these data are coming from and how you are entering/importing them into Excel.

[scavia]

Thanks for the help. Now that you pointed out the "13th month" problem, I see what's going on. I parsed that string to create an actual date and it all worked out. I didn't create the original spreadsheet. It was given to me from a Canadian, which explains the day-month/month-day issue!