I have a dataset similar to below in google sheets that I want to plot in a line chart with dates on the x-axis and time on the y-axis.
Date Time 2023-01-01 23:46 2023-01-02 00:32 2023-01-03 00:16 2023-01-04 22:37 2023-01-05 23:59
As you can see the times varies around midnight (00:00) which does not go well with a ordinary line chart since the 00:16 will be in the bottom of the chart and 23:59 will be at the top of the chart even though its only 17 minutes apart. How can I correct this to make a "better" and smoother graph?
I have managed to make it more smooth by either subtracting or adding 12h (23:59 becomes 11:59 and 00:16 becomes 12:16 for the actual y-values) so that the graph instead "oscillates" around noon instead of midnight, then the graphical representation is correct but the actual values on the y-axis is still wrong. How can I solve this problem to make a more smooth graph and correct time values on the y-axis?
A possible workaround is to add 1 if the time is after midnight (i.e. before 12 noon the next day, which is represented as 0.5 days):