Перейти до основного матеріалу

Як правильно відобразити / показати негативний час в Excel?

Деякі з нас можуть зазнати цієї проблеми, коли ви віднімаєте пізніший час 12:20 від попереднього часу 10:15, ви отримаєте результат як помилку ######, як показано на наступних скріншотах. Як у цьому випадку ви могли правильно та нормально відображати негативний час у програмі Excel?

doc-show-negative-time1-2doc-show-negative-time2

Відображуйте негативний час належним чином, змінюючи систему дат за замовчуванням у Excel

Правильно відображати негативний час за допомогою формул

стрілка синя права міхурВідображуйте негативний час належним чином, змінюючи систему дат за замовчуванням у Excel

Ось простий і швидкий спосіб відобразити негативний час, як правило, в Excel, змінивши систему дат за замовчуванням на Excel на 1904. Будь ласка, зробіть так:

1. Щоб відкрити Параметри Excel діалогове вікно, клацнувши філе > Опції в Excel 2010/2013 та клацніть Кнопка Office > Параметри Excel в Excel 2007.

2. Потім у Параметри Excel діалогове вікно натисніть кнопку Advanced на лівій панелі, а в правому розділі поставте галочку Використовуйте систему дат 1904 при Під час обчислення цієї книги розділ. Дивіться знімок екрана:

doc-show-negative-time1

3. Після завершення налаштувань натисніть OK. І негативний час буде відображатися відразу правильно, див. Скріншоти:

doc-show-negative-time1-2doc-show-negative-time2

стрілка синя права міхурПравильно відображати негативний час за допомогою формул

Якщо ви не хочете змінювати систему дат, ви також можете скористатися наступною формулою для вирішення цього завдання.

1. Введіть дати, які ви хочете обчислити, і введіть цю формулу = ТЕКСТ (МАКС. ($ A $ 1: $ A $ 2) -МІН ($ A $ 1: $ A $ 2), "- H :: MM") (А1 і ​​А2 позначають дві часові клітинки окремо) у порожню клітинку. Дивіться знімок екрана:

doc-show-negative-time1

2. Потім натисніть Що натомість? Створіть віртуальну версію себе у і ви отримаєте правильний результат, як показано нижче:

doc-show-negative-time1

Порада:

Ось ще одна формула, яка також може вам допомогти: = IF (A2-A1 <0, "-" & TEXT (ABS (A2-A1), "hh: mm"), A2-A1)

У цій формулі A2 позначає менший час, а A1 означає більший час. Ви можете змінювати їх, як вам потрібно.

Змініть усі цифри на позитивні чи негативні, як вам потрібно в Excel
В Excel список чисел, змішаних з негативними і позитивними значеннями, які потрібно було перетворити як позитивні чи негативні, в даному випадку, який метод є найкращим способом швидкого та точного вирішення завдання Змінити знак цінностей корисність Kutools для Excel буде вдалим вибором. Натисніть, щоб отримати повну безкоштовну пробну версію протягом 30 днів!
doc змінити знак
 
Kutools для Excel: із понад 300 зручними надбудовами Excel, які можна безкоштовно спробувати без обмежень протягом 30 днів.

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями  |   Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул   |  Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок   |  Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів:  12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис

Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (18)
No ratings yet. Be the first to rate!
Martin
Martin
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?
Reply
0
0
Ayolice
Ayolice
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?
Reply
Report
0
0
Kunal Shah
Kunal Shah     Ayolice
This comment was minimized by the moderator on the site
-0:00:05
-0:00:10
0:00:03
=sum(A1:A3)
Reply
0
0
Sunny
Sunny     Ayolice
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")
Reply
Report
0
0
JeteMc
JeteMc
This comment was minimized by the moderator on the site
Thank You!
Reply
0
0
shalene
shalene
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!
Reply
0
0
John M
John M     shalene
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.
Reply
0
0
JS
JS
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.
Reply
0
0
KulGery
KulGery     JS
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))
Reply
0
0
bo ek
bo ek
This comment was minimized by the moderator on the site
thank you so much! You just saved my day!
Reply
0
0
Meg
Meg
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?
Reply
0
0
Marc Kupper
Marc Kupper     Meg
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
Reply
0
0
MND
MND
This comment was minimized by the moderator on the site
"Tip: Here is another formula also can help you: =IF(A2-A1
Reply
0
0
Ralf
Ralf
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?
Reply
0
0
Marc Kupper
Marc Kupper     Ralf
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.
Reply
0
0
Ralf
Ralf     Marc Kupper
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 ...
Reply
0
0
sk
sk
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
Reply
0
0
ExelWizard
ExelWizard     sk
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?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations