英文:
How to add large quantities of seconds data to a timestamp in Excel?
问题
我有一些传感器数据，这些数据被输出到一个Excel文件中。不幸的是，这个程序在测试开始时生成一个时间戳，然后跟踪数据从那个初始时间戳开始以秒为单位的进展。
出于分析目的，我希望能够将所有的时间从秒数转换为时间戳，以便更好地绘制数据。
以下是我的数据示例。输出文件在测试开始时生成一个时间戳，例如 [2023-04-21 4:03:41 PM]（奇怪的是，在我的Excel中，我不能格式化单元格以显示 yyyy-mm-dd h:mm:ss，我只有选择以 yyyy-mm-dd h:mm 的方式显示），在另一个表中，“Time”列显示了从测试开始以秒为单位的时间。我手动添加了一个“Timestamp”列，希望在其中添加从秒数到时间戳的转换。
这是我迄今为止使用的添加秒数到初始时间戳的方程式：
= 2023-04-21 16:03 + TIME(0,0,A2)
例如，如果测试在 [2023-04-21 4:03:41 PM] 开始，然后产生了一行数据，延迟了2500秒，那么这一行数据的新时间戳是2023-04-21 16:45。
这在秒数达到32767之前都很好；在这个数字之后，我会得到一个 #NUM 错误。请参见下面的情况：
这是TIME公式的限制：
因此，我想知道是否有一种公式/方法，可以将时间添加到时间戳，无论我有多少秒（随着时间的推移，这个数字会变得非常大）。
谢谢你的帮助。
英文:
I have some sensor data that is being outputted in an Excel file. Unfortunately, this program produces one timestamp when the test starts and then tracks the progression of the data overtime since that initial timestamp in seconds.
For analytical purposes, I would like to be able to convert all of the time from time in seconds into timestamps to be able to better graph the data.
Here is an example from my data. The output file produces a timestamp when the test starts, which in this case is [2023-04-21 4:03:41 PM] (oddly in my excel I can't format the cell to show yyyy-mm-dd h:mm:ss, I only have the option to do yyyy-mm-dd h:mm) and in another sheet the "Time" column has the time since the test starts in seconds. I manually added a "Timestamp" column where I hope to add the conversion from seconds to timestamp.
Here is the equation that I have used to add the seconds to the initial timestamp so far:
= 2023-04-21 16:03 + TIME(0,0,A2)
For example, if the test started at [2023-04-21 4:03:41 PM], and one row of data is produced 2500 seconds later, then the new timestamp produced for this row of data is 2023-04-21 16:45.
This is great up until the seconds reaches the number 32767; up until this number, the formula works, after this number, I get a #NUM error. Please see below:
For this TIME formula, Excel does have this limit:
Therefore, I'm wondering if there is a formula/method that I can use that will add the time to the timestamp, no matter how many seconds I have (since overtime this number will get very big).
Thanks for your help.
答案1
得分: 1
你可以使用模运算将增量拆分为小时、分钟和秒，然后将其传递给TIME函数。例如，如果你的增量在单元格A2中，你可以使用：
=（初始开始时间）+ LET（增量，A2，秒，MOD（增量，60*60），分，MOD（（增量-秒）/60，60），小时，（增量-分-秒）/（60*60），TIME（小时，分，秒））
这应该能够逐秒添加增量，直到增量达到117,961,200秒，大约3.75年。Excel不支持在单个工作表中有那么多行，所以到那个时候你可能需要采取不同的方法。
英文:
You can use modular arithmetic to split the increment into hours, minutes, and seconds before passing it to the TIME function. For example, if your increment is in cell A2, you can use:
= (initial start time) + LET(increment, A2, sec, MOD(increment, 60*60), min, MOD((increment-sec)/60, 60), hour, (increment-min-sec)/(60*60), TIME(hour, min, sec))
This should be able to add the second-wise increment until the increment reaches 117,961,200 seconds, which is around 3.75 years. Excel does not support that many rows in a single sheet, so you would probably need a different approach by that time anyway.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论