You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I am likely being complete numpty, however....
I am collating journey durations, with format [hhh]:mm, as some of these are greater than 24hrs.
However, Excel does not seem to allow me to create any graphs or charts...
Am I doing something wrong??
Thanks all.
Converting to minutes will probably make life easier?
Works for me?
You don't need [hhh] unless you want it in the format 001, 002 etc. You only need dd/mm/yyyy to preserve the zeros.
I think you have to put a date in the cell values as well as time for the charting stuff to work.
Might be fixed in later versions but I remember having to do that previously.
Excel will generate appropriate graphs provided that it 'knows' the data is in the form of time. If it just thinks your "hhh:mm" entry is a character string (i.e. text) it'll be unable to interpret it.
Have a look at the 'time' function. E.g. '=Time(some stuff)'
Try running text to columns on your duration column, just open the dialog and click finish, no need to change any settings.
Should prompt it to figure out your times are times and not text.
I've just tested it and it works for me, just entering hh:mm. If you right click on a cell with time in it and pick Format Cells, they should appear as either Time or Custom hh:mm.
However, I can't get it to display the hh part as over 24 - it just subtracts 24 as if it's time of day. But you can then format it as dd:hh:mm and it works.
@Greybeard that's what the [h]:mm format option is for.
Typing it in it should pick it up automatically but I've had this problem with imported data before where it's treating the data as a string and ignoring format changes. Text to columns fixes it.
that’s what the [h]:mm format option is for
Ha, so it is. You never stop learning. That might help the OP, too.
Same works for days e.g. [d] gives you number of days rather than jan 1st 1901, jan 2nd etc.