Search code examples
excelplotexcel-formulaexcel-2010

Forcing order with time format on Excel

I run a test overnight and have the results written on a txt file that I later extract the time and value from. The result is something like this:

Time data

Because it goes overnight and the time resets, the resulting plot is this:

Out of order plot

Is there any way to force the order in which the values are plotted without modifying the original .txt file so that it looks like my daytime tests?

in order plot

Edit: Indeed the actual data skips and the timestamps are not periodic, here's how it looks around midnight

Not periodic timestamps

Solution

  • You will need a helper column and you need to make sure your time is actual excel time, and not a time string. Assuming your time is in column B starting in cell B2 you can use the following process:

    1) Test cell

    =ISNUMBER(B2)

    If that is TRUE then you know you have your time stored in excel format. In excel time is stores as a decimal which represents the portion of a day. 12 noon is 0.5. Dates are stored as integers and represent number of days since 1900/01/01 with that date being day 1.

    2) Add 24 hour increment

    In order to properly sort your order for graphing purposes, you will need to add a day everytime your data passes the 24 / midnight mark. Assuming your data is sorted chronologically as per your example, use the following formulas in lets say column C

    First cell C2
=B2

In C3 and copy down
=IF(B2>B3, B3+1,B3)
which can be rewritten as
=B3+(B2>B3)

    Now format the cells in C for time. The date will not display, only time will. The times after the 23:59:59 mark will all be the following day.