跳到主要内容

如何在Excel中正确显示/显示负时间?

我们中的某些人可能会遇到此问题,当您从较早的时间12:20减去较晚的时间10:15时,您将得到如下错误所示的结果,即######错误。 在这种情况下,如何在Excel中正常正常显示负时间?

文档显示负时间1-2文档显示负时间2

通过更改Excel的默认日期系统正确显示负时间

使用公式正确显示负时间


箭头蓝色右气泡通过更改Excel的默认日期系统正确显示负时间

通过将Excel的默认日期系统更改为1904年日期系统,这是一种简单快速的方法,可用于在Excel中正常显示负时间。 请这样做:

1。 打开 Excel选项 通过单击对话框 文件 > 附加选项 在Excel 2010/2013中,然后单击 办公按钮 > Excel选项 在Excel 2007中。

2。 然后在 Excel选项 对话框,单击 高级 从左侧窗格的右侧部分中,检查 使用1904年日期系统计算此工作簿时 部分。 看截图:

文档显示负时间1

3.完成设置后,单击 OK。 负时间将立即正确显示,请参见屏幕截图:

文档显示负时间1-2文档显示负时间2


箭头蓝色右气泡使用公式正确显示负时间

如果您不想更改日期系统,也可以使用以下公式来解决此任务。

1。 输入您要计算的日期,然后输入此公式 = TEXT(MAX($ A $ 1:$ A $ 2)-MIN($ A $ 1:$ A $ 2),“-H :: MM”) (A1和A2分别表示两个时间单元)变成一个空白单元。 看截图:

文档显示负时间1

2。 然后按 输入 密钥,您将获得正确的结果,如下所示:

文档显示负时间1

提示:

这是另一个公式也可以帮助您: = IF(A2-A1 <0,“-”&TEXT(ABS(A2-A1),“ hh:mm”),A2-A1)

在这个公式中, A2 表示时间较短,并且 A1 代表更长的时间。 您可以根据需要更改它们。


在Excel中根据需要将所有数字更改为正数或负数

在Excel中,一个包含负值和正值的数字列表,需要转换为正数或负数,在这种情况下,哪种方法是快速而准确地解决工作的最佳方法? 改变价值的标志 实用程序 Kutools for Excel 将是一个不错的选择。 点击即可获得 30 天的全功能免费试用!
文档变更标志
 
Kutools for Excel:拥有300多个便捷的Excel加载项,可以在30天内免费试用。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The problem I'm having with this negative time solution - is that the spreadsheet with the negative also has a lot of cells with dates in it, so when I Tick 1904 Date System - This adds 4 years to the dates.... is the only remedy for this to go and manually change all those dates?
This comment was minimized by the moderator on the site
I used the formula =IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1) to calculate the difference between times and it fixed my problem, so thank you! However, now I have another problem, I need to add together the results. So for example, if I have three different times I need to add up (as a result of the formula): -00:00:05, -00:00:10 and 00:00:03. So 2 negative times and 1 positive in this example. How can I have a formula that will calculate (-00:00:05) + (-00:00:10) + (00:00:03) = -00:00:12?
This comment was minimized by the moderator on the site
-0:00:05
-0:00:10
0:00:03
=sum(A1:A3)
This comment was minimized by the moderator on the site
Hi, Ayolice, place the positive time in cell E1, and place the negative time in cell E2 and E3, then use this formula =TEXT(MAX($E$1:$E$3)-MIN($E$1:$E$3),"-H::MM:SS")
This comment was minimized by the moderator on the site
Thank You!
This comment was minimized by the moderator on the site
i have a date from someone elses file showing as "20171229" i need it to look like "12/29/2017".. PLEASE HELP!
This comment was minimized by the moderator on the site
=date(left(A1,4),mid(A1,5,2),right(A1,2)
Where A1 is the cell containing the date.

The new cell will contain an excel date value for that day,
right click the new cell, and select the date format you want.
This comment was minimized by the moderator on the site
I have a negative time value in a cell, say CQ15. [ i got the value using the above mentioned formula =IF(A2-A1<0, "-" & TEXT(ABS(A2-A1),"hh:mm"), A2-A1) ]

Next, I'm trying to multiply the cell value with 5, ie. CQ15*5.
But not getting any result.
I have tried both the following formulas:
=TEXT(CQ15*5, "[h]:m:ss")
&
=IF(CQ15*5<0, "-" & TEXT(ABS(CQ15*5),"[h]:m:ss"),CQ15*5)

In both cases, i received #VALUE!

Can anyone please help me with this.
This comment was minimized by the moderator on the site
Couse CQ15 is a text cell. For calculate with it, you must format it as time value.
=IF(LEFT(CQ15,1)="-", -TIMEVALUE(RIGHT(CQ15,LEN(CQ15)-1)), TIMEVALUE(CQ15))
This comment was minimized by the moderator on the site
thank you so much! You just saved my day!
This comment was minimized by the moderator on the site
I am trying to calculate a negative formula for shift time not fully worked. Say data is as follows: A1 = 07:30 shift start, minus A2 = 1:00 lunch break A3 = 16:30 shift ends Then A4 = 8:00 hours worked during the day. But, should the worker leave earlier, say 17:00, what is the formula in cell A5 that shows how much short the person's shift was?
This comment was minimized by the moderator on the site
[quote]I am trying to calculate a negative formula for shift time not fully worked. Say data is as follows: A1 = 07:30 shift start, minus A2 = 1:00 lunch break A3 = 16:30 shift ends Then A4 = 8:00 hours worked during the day. But, should the worker leave earlier, say 17:00, what is the formula in cell A5 that shows how much short the person's shift was?By Meg[/quote] You can't directly display a negative time in Excel. However, you can display it using the TEXT() function. For example Cell A4 would have =A3-A1-A2 which will display the hours/minutes worked. Cell A5 would have 8:00 which is the number of hours you expect this person to work Cell A6 would have =IF(A5=A4,"Met expected target",IF(A4
This comment was minimized by the moderator on the site
"Tip: Here is another formula also can help you: =IF(A2-A1
This comment was minimized by the moderator on the site
If you switch to 1904 all dates in the workbook change. Not a solution. What is this 1904 thing anyway?
This comment was minimized by the moderator on the site
[quote]If you switch to 1904 all dates in the workbook change. Not a solution. What is this 1904 thing anyway?By Ralf[/quote] The 1904 format comes from Excel version 1.0 which was on the Macintosh. Two years later Microsoft released Excel for Windows and with with that release introduced the "1900" date format. Excel for the Mac continued to default to the 1904 format and Windows defaults to the 1900 format. Both versions can switch to the other format but doing so will cause all dates in a workbook to shift. The year 1900 was special in that though it's divisible by 4 it was not a leap year. The Mac's clock hardware did not have the extra logic needed to handle this special case and so Apple simplified the design by allowing for dates from 1904 (the first leap year in the century) on out. As Excel was first written for the Mac it was natural that Excel would use the Mac's date format. The developers of Excel for Windows used the 1900 format for backwards compatibility with SuperCalc and Lotus 1-2-3 which were the dominate spreadsheet applications in the MS-DOS and Windows markets at the time. The 1904 and 1900 formats are nearly identical. The 1904 format stores dates as the number of days since 1/1/1904 and the 1900 format is the number of days since 1/1/1900. You can convert a 1904 date to a 1900 date by adding 1,462 to a cell's value and subtracting 1,462 to convert the other way. Apparently the developers of Excel for the Mac included code to support negative time values. The coders of Excel for Windows did not support negative time values. Modern versions of Excel still include two separate chunks of code with one dealing with 1904 dates and the other for 1900 dates. They never fixed the 1900 code to support display of negative time values.
This comment was minimized by the moderator on the site
Thanks for the history lesson, Marc. Much clearer now how it came to this. Still leaves me wondering about your last sentence: "They never fixed ..." I don't get it. Would it be so hard to fix? So I still need to use Libre Office for two things: negative times and CSV export. Must be exotic niche features ...
This comment was minimized by the moderator on the site
Hello, any idea of how to do sum of negative hours which are in hh:mm format? for example -01:00 is at A1 column, -02:00 is at A2 column, -03:00 is at A3 column. I used "if" command to sum up the three columns (A1,A2,A3) but showing 0:00 if command "if(A1:A3
This comment was minimized by the moderator on the site
Couldn't you just multiply the numbers by -1 to start, add them up then multiply by -1 again?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations