与Excel相比，Google电子表格的日期/时间中断了吗? [英] Google spreadsheet dates/times broken compared to Excel?

问题描述

为另一个与日期/时间相关的帖子表示歉意.我还没有解决这个问题，因此欢迎提出任何意见. 执行摘要:Excel可以用于处理较短的时间(运动事件时间等)，但Sheets不能，因为您需要编写脚本来显示较小的时间，并且脚本将内容弄乱了，以使Sheets不再将单元格视为以后手动更改时间的时间.

Apologies for yet another date/time related post. I haven't got my head around this yet, so any comments welcome. Executive summary: Excel can be used to work with small elapsed times (athletic event times, and so on), but Sheets can't, because you need to write a script to display small times, and the script screws something up so that Sheets no longer treats the cell as a time when the time is later manually changed.

我需要的时间通常很短，大约几分钟，几秒到两位小数.我必须在这些时间上进行算术运算(通常仅是与其他时间进行比较).

The times I need are normally short, in the region of a couple of minutes, with seconds to 2 decimal places. I have to do arithmetic on these times (normally only comparisons to another time).

我们目前将所有内容存储在Excel中，效果很好.如果您在一个范围内定义自定义显示格式，然后以类似2:43.02的形式输入时间(2分钟，即43.02s)，则该单元格仍包含一个时间，Excel可以对这些时间进行算术运算.

We currently store everything in Excel, which works fine. If you define a custom display format in a range, and then enter times as something like 2:43.02 (2 mins, 43.02s), then the cell still contains a time, and Excel can do arithmetic on those times.

然后我上传Excel电子表格，并在表格中打开它.没有内置的格式扩展名，就像Excel中一样，因此我必须使用扩展名功能将时间显示为2:43.02(请参见下文；请参见setNumberFormat).在新的Sheets版本中，Excel时间仍然是时间，并且比较有效，并且一切正常.

I then upload the Excel spreadsheet, and open it in Sheets. There is no built-in formatting extension, as there is in Excel, so I have to use an extension function to display the time as 2:43.02, for example (see below; the function calls setNumberFormat). In the new Sheets version of the document, Excel times are still times, and comparison works, and everything looks right.

这是问题所在.除非您在表格"单元格中手动输入新时间，否则一切都将起作用.例如，如果输入2:41.05，则该单元格将不再包含时间对象，并且所有算术都将被破坏.因此，我不能使用表格代替Excel.我必须将所有内容保留在Excel中并偶尔上传，以将Sheets版本保持为只读状态.

Here's the problem. Everything only works until you manually enter a new time into a Sheets cell. If I enter 2:41.05, for example, the cell no longer contains a time object, and all arithmetic is broken. So, I can't use Sheets to replace Excel. I have to keep everything in Excel and upload it occasionally, keeping the Sheets version as read-only.

表格中有一些魔术可以确定单元格是否包含日期.如果我使用内置的时间格式，则Sheets知道这是一个时间，如果手动更改了时间，则可以进行算术运算.如果我使用以下功能更改时间格式(来自此处)，那魔力就消失了，我无法手动更改时间.

There's some magic in Sheets which determines whether a cell contains a date or not. If I use a built-in time format, then Sheets knows it's a time, and can do arithmetic if the time is manually changed. If I change the time format using the function below (from here), then that magic is lost, and I can't manually change the time.

function EditFormat() {
  var oldname = SpreadsheetApp.getActiveRange().getNumberFormat();
  var name = Browser.inputBox("Current format (blank = no change):\r\n"+ oldname);
  SpreadsheetApp.getActiveRange().setNumberFormat((name=="")?oldname:name);
}

问题:setNumberFormat是否损坏?有没有更好的方法来完成所有这些工作?我可以在脚本中做一些其他事情来保留单元格的魔法状态吗?还是我必须回到Excel?

Question: is setNumberFormat broken? Is there a better way to do all this? Can I do something else in the script which retains the cell's magic status as a time? Or do I have to go back to Excel?

谢谢.

推荐答案

这是由于(长期存在的)Sheets错误所致，解析器在两个方面都失败了:

This is due to a (sadly long-standing) Sheets bug whereby the parser fails in two respects:

1. 格式2:43.02未成功强制转换为时间值，但格式为0:02:43.02(或0:2:43.02).
2. 即使如此，直接输入的值也将忽略百分之一秒.

两个点都适用于电子表格中的所有值，但是只有两个点适用于直接输入的值.因此，一种解决方法是通过公式输入:

Both points apply for all values in the spreadsheet, however only point two applies for directly entered values. So a workaround is to enter via a formula:

=VALUE("0:2:43.02")

或者您可以将输入列设置为纯文本格式，然后将显示列类似地通过公式转换为时间值(并且该显示列将被自定义格式).或者，您可以有一个帮助程序脚本，该脚本为活动单元格提供一个输入框，您在其中输入时间段，然后创建如上的公式并将其填充到单元格中(可以将内置数字格式内置到此脚本中)也是如此).

or you could have an input column that is formatted as plain text, and then have a display column that similarly converts to a time value via formula (and this display column would be custom formatted). Or, you could have a helper script that presents an input box for the active cell, where you enter the split time, and then creates a formula like above and populates it in the cell (the set number format could be built in to this script as well).

我希望这些变通办法是不必要的.

I wish these workarounds were unnecessary.

这篇关于与Excel相比，Google电子表格的日期/时间中断了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！