Google Sheets - Chart Duration Error

Question

I am trying produce a burn chart in google sheets to record time remaining on a project, which has to be formatted as 'duration' but any data over 24 hours plots incorrectly.

It seems to be wrapping values over 24 hours back on themselves, so for example 30:00:00 plots at 06:00:00 (which is 30-24). So it seems to be treating the 'duration' as if they're numbers on a clock when I need them to work as normal numbers - i.e. go over 24 hours.

Here's the Sheet

and a screenshot:

Can anyone help?

Answer 1

It seems you found a bug in Google Sheets, googling for the problem finds other people with the same issue, e.g. here: https://productforums.google.com/forum/#!topic/docs/tf_ehe6J-CU

The workaround is to multiply the values by 24 to get the hours as a number. It will not be perfect, as it will show fractions of an hour after the dot, instead of proper minutes (you could implement a text formula to get the minute values e.g. as data point values if you really want to, but probably not on the Y axis). For most real life projects I'm guessing that should be good enough.