google sheetsgoogle-sheets-charts
I have created a Google Sheet that collects a bunch of values in columns. One of these is a time value formatted hh:mm:ss AM/PM, obtained with the following formula:
=TIMEVALUE(SUBSTITUTE("April 28, 2019 at 02:03PM"," at ", " "))
However, these values are shown incorrectly in the chart:
Apparently, I can't find a way to customise time format in the chart… How should I fix this?
There is not normally any difference between sorting on values as such or sorting on the results of formulae. Column A shows the result of applying weekday to the chronologically entered values in ColumnB. D:E is a copy of A:B but sorted on Column D ascending.
There may be issues however with certain formulae that use the likes of Row() or offset, in which case it would help if your Q specified or gave an example of such formulae.
The new behavior is likely due to an internal change since the last update introducing the new Google Sheets. My suggestion would be to add an intermediary sheet to connect to the commute chart. The new sheet would contain linked values from the Times sheet. You could then perform arithmetic and formatting operations on it while keeping the Times sheet untouched. At the end, you could also hide the supporting sheet.
An example: https://docs.google.com/spreadsheet/ccc?key=0AvNKw_kDnGPQdHVncl9BNkY0cGIteHpjNVZla0RVMVE&usp=sharing#gid=4
The downside is that the time representation you used to achieve on the chart may no longer be possible. In fact, once formatted the duration values become single points in time instead of differences between times.
Best Answer