Calculating time

rookzie

New Member
Joined
Mar 25, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have been racking my head on how to solve my time issue on my train sheet.

I have a train time sheet to show if a train is on time or late Ex;

if a train is set and ready to go at 01:30 and the crew is ordered at 03:30 i can show it to say 2:00 on time between these two times. but lets say the train wasn't ready to leave until 04:12 but the crew was ordered at 03:00 i need it to show -1:12 train was late. what i get is #######

i hope i'm explaining this right.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
This could be a way, but with negative values ​​you will not be able to perform operations.
I hope it helps you.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">04:12</td><td style="text-align:right; ">03:00</td><td >-1:12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01:30</td><td style="text-align:right; ">03:30</td><td style="text-align:right; ">02:00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(A2<B2,B2-A2,"-" & TEXT(A2-B2,"h:mm"))</td></tr></table></td></tr></table>
 
Upvote 0
To use negative time, you must switch to the 1904 time system.

You can use the subtraction and the negative type can also be used to perform operations.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">04:12</td><td style="text-align:right; ">03:00</td><td style="text-align:right; ">-01:12</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">01:30</td><td style="text-align:right; ">03:30</td><td style="text-align:right; ">02:00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">00:48</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=B2-A2</td></tr><tr><td >C3</td><td >=B3-A3</td></tr><tr><td >C4</td><td >=SUM(C2:C3)</td></tr></table></td></tr></table>
 
Upvote 0
And just embellishing DanteAmor's response, don't forget that you can use the TEXT format "hh:mm" and right horizontal cell format so that the "negative time" lines up with normally-calculated positive times.

Also note that you will not be able to reference the "negative time" text directly in other arithmetic; for example, part of the sum of total time that the crew has been assigned.
 
Upvote 0
Also note that you will not be able to reference the "negative time" text directly in other arithmetic; for example, part of the sum of total time that the crew has been assigned.

I meant: directly reference. We can always provide a formula. Consider using two cells: the visible one with DanteAmor's formula, and another cell (hidden?) with your original calculation formatted as General. Refer to the latter in any time arithmetic.

And a head's up: someone might suggest setting the 1904 Date System option. I advise against it. It changes the "serial number" that represents any date. That can cause problems when copying between workbooks.

(Oh, I didn't notice that DanteAmor did already. My comment was not meant as a direct criticism. But I do believe it is not a good idea.)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top