0

some time ago I ported an old timesheet to the google-timesheet to be able to share it online and have others modify it but didn't keeped it so I don't remember how I manage to do.

The goal is to compare two timing sheet, today I wanted to edit the sheet to be able add new datas and write timing in separate page sheet which simplify the comparation as I'll just have to import the data from the page and not rewrite it all the time. My issue is that I'm not enable to replicate on my new pages the format. What I would like is to have this exemple working

Cities Time
Helsinki 2:04.820
Travemünde 4:03.290
Hambourg 0:30.900
Hanovre 2:28.610
Francfort 4:53.470
Mannheim 1:35.170
Strasbourg 2:13.650
Berne 2:25.190
Genève 2:22.620
Lyon 2:24.000
Marseille 3:34.550
Marseille (ferry)
Palerme 2:28.670
Catania 4:07.670
Total =SUM(above)

so that I can replicate the format on the other pages as I don't understand why it worked before but not now.

mm:ss.000 is the format I would like to have, but atm my format is [h]:mm:ss.000 and it seems that the hour markdown is necesary so I don't really mind if we need to keep the hour.

Pierre Viprey
  • 3
  • 1

2 Answers2

0

This is completely possible with google sheets. Enter the data in the format hh:mm:ss.ms, and use sum() (with a range, obv.) to sum the column. Then select the whole colume and apply a custom number format (data-> custom time/date). Using the dropdown to get the parts and typing the separators you can get Minute(1): Seconds(1).Milliseconds(3) which seems to be what you want.

For sheets to recognise the cell entry as a time it needs the hh: part. But you can certainly hide that in the display.

Demo Spreadsheet

If the spreadsheet locale is set to something which uses , for the decimal point, you need to use that instead of . Google could definitely make that a lot clearer. If you have the time you might even want to open a bug report with them, as the examples in their docs don't work when the locale requires a ,.

As a bonus, you can bulk-convert using a formula like =replace(B2; find("."; B2); 1; ",") * 1 (where B2 is the cell to be converted). Drag down, copy and paste the values, and then format if need be.

2e0byo
  • 5,305
  • 1
  • 6
  • 26
  • @PierreViprey where's the new vs. old data? – 2e0byo Jul 10 '22 at 17:48
  • Good point, I have renamed the pages to add the info about the new and old datas. – Pierre Viprey Jul 10 '22 at 17:59
  • @PierreViprey the trouble is that you're not English! or rather that the your locale isn't en-{something}. So you want the European `,` not `.` for decimals. That was a *really* odd problem and completely counterintuitive of google--but the spreadsheet you shared keeps its own locale. – 2e0byo Jul 10 '22 at 18:18
  • @PierreViprey I've added a formula for bulk conversion if it helps. What a bizarre problem, though! – 2e0byo Jul 10 '22 at 18:25
0

use:

=ARRAYFORMULA(TEXT(SUM(IFERROR(TIMEVALUE("0:"&B1:B15))); "[m]:ss.000"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124