跳到主要內容

如何在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。 然後按 Enter 密鑰,您將獲得正確的結果,如下所示:

文檔顯示負時間1

小提示:

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

在這個公式中, A2 表示時間較短,並且 A1 代表更長的時間。 您可以根據需要更改它們。


在Excel中根據需要將所有數字更改為正數或負數

在Excel中,一個包含負值和正值的數字列表,需要轉換為正數或負數,在這種情況下,哪種方法是快速而準確地解決工作的最佳方法? 改變價值的標誌 的效用 Excel的Kutools 將是一個不錯的選擇。 點擊即可獲得 30 天的全功能免費試用!
文檔變更標誌
 
Kutools for Excel:擁有300多個便捷的Excel加載項,可以在30天內免費試用。

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為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