日期/时间的字符串表示形式之间的差异

发布于 2024-08-12 20:12:15 字数 367 浏览 7 评论 0原文

在 Excel 2007 中，我想要以下字符串日期/时间的差异：

  A                         B                          C
1 Date1                     Date2                      Difference of A and B
2 2009.11.28 01:25:46:0287  2009.11.28 01:25:46:0287   ?
3 2009.11.28 01:25:46:0443  2009.11.28 01:25:46:0443   ?

我想要通过将字符串转换为日期/时间来获得差异，然后将结果作为两个转换后的日期/时间的差异。

原文

In Excel 2007, I want the differences for the following string date/times:

  A                         B                          C
1 Date1                     Date2                      Difference of A and B
2 2009.11.28 01:25:46:0287  2009.11.28 01:25:46:0287   ?
3 2009.11.28 01:25:46:0443  2009.11.28 01:25:46:0443   ?

I want the differences by converting strings to date/time and then the results as differences of two converted date/times.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

合久必婚 2024-08-19 20:12:15

计算零的相当冗长的方法（对于示例）：

=SUBSTITUTE(LEFT(A2,10),".","/")+MID(A2,12,8)+RIGHT(A2,4)/86400000-(SUBSTITUTE(LEFT(B2,10),".","/")+MID(B2,12,8)+RIGHT(B2,4)/86400000)

根据特殊要求，并且稍微短一些：

=SUBSTITUTE(LEFT(A2,10),".","/")+REPLACE(RIGHT(A2,13),9,1,".")-(SUBSTITUTE(LEFT(B2,10),".","/")+REPLACE(RIGHT(B2,13),9,1,"."))

A rather long-winded way to calculate zero (for the examples):

=SUBSTITUTE(LEFT(A2,10),".","/")+MID(A2,12,8)+RIGHT(A2,4)/86400000-(SUBSTITUTE(LEFT(B2,10),".","/")+MID(B2,12,8)+RIGHT(B2,4)/86400000)

By special request and very slightly shorter:

=SUBSTITUTE(LEFT(A2,10),".","/")+REPLACE(RIGHT(A2,13),9,1,".")-(SUBSTITUTE(LEFT(B2,10),".","/")+REPLACE(RIGHT(B2,13),9,1,"."))

回复收藏 0 原文

记忆里有你的影子 2024-08-19 20:12:15

我想不出一个真正好的方法来做到这一点......希望其他人能想到。话虽如此，以下内容可能会满足您的需求。

要转换主日期部分，请使用以下公式（假设字符串日期位于 A1 中）：

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)) +
    TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

要转换第二小数部分，请使用：

=VALUE(MID(A1,21,4))/10000

日期/时间部分可以轻松减去，第二小数部分也可以。

我遇到麻烦的地方是将这些部分重新组合成一个整体，Excel 实际上会以合理的方式显示。最后，我将两个日期之间的差值乘以 86400（= 24 * 60 * 60 - 典型一天中的秒数），然后将差值添加到小数第二部分中。

希望这有帮助。
问候，
理查德

·P.S.这个解决方案有很多我不喜欢的地方，其中最大的是公式的脆弱性 - 如果日期格式发生变化，公式将需要调整。

I couldn't come up with a really nice way of doing this... hopefully someone else will. Having said that, the following may give you what you need.

To convert the main date part, use the following formula (this assumes the string date is in A1):

=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)) +
    TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

To convert the fractional second part, use:

=VALUE(MID(A1,21,4))/10000

The date / time part can easily be subtracted, as can the fractional second part.

The place I ran into trouble was recombining those parts into a whole that Excel will actually display in a sensible way. I finally took the difference between the two dates and multiplied by 86400 (= 24 * 60 * 60 - number of seconds in a typical day) then added the difference in the fractional second part.

Hope this helps.
Regards,
Richard

P.S. There are quite a lot of things I don't like about this solution, the biggest of which is the fragility of the formulas - if the date format changes, the formulas will need to be adjusted.

回复收藏 0 原文

~没有更多了~