Calculate extra and lost time (negative time) 1

[OOzy]

Dears

I have three columns as follows:

In Out losttime
--- ----- --------
8:04 15:10
7:11 14:00

The above is when someone report to work. As you all know we need to work 8 hours. Now I would like to do the following 8:00-(out-in) but this sometimes gives a negative time. How can I do this and have the output (losttime or extra time) to appear the format hh:mm. Notice that 8:00 is eight hours not 8:00 AM.
w

 

[Ted1]

I'd suggest that you complete your third column to indicate net hours worked in hh:mm format but then work in hours and decimals of an hour to complete any further calculations (including subtracting your standard 8 hours).

The conversion is done by multiplying your net hh:mm by 24 and placing the result into a numerically formatted columm .

You can then subtract 8.00 hours and proceed as you wish.

Ted1

Time in Time outTime worked credit/debit
((columnC*24)-8)decimalised)
05:00 14:25 09:25 1.42
06:00 14:25 08:25 0.42
07:00 14:00 07:00 -1.00
08:00 14:25 06:25 -1.58

 

[byundt]

If you change the date display to the 1904 date system (using the Tools...Options...Calculation menu item), then negative times can be displayed. You may not want to do this if you are exchanging the file with other users who use the 1900 system instead, however.

Alternatively, you can use a VBA function to return either the difference as a normal date/time serial number (if it is positive) or a string formatted to look like a negative time (if it is negative). Install the function in a regular module sheet, then call it with a worksheet formula like:
=TymeFormatter(A2-A1)

Function TymeFormatter(tyme As Double) As Variant
Dim dTime As Double
Dim sFormat As String
sFormat = Application.Caller.NumberFormat
dTime = Abs(tyme)
TymeFormatter = tyme
If tyme < 0 Then TymeFormatter = Format(dTime, "-" & sFormat)
End Function

Brad

 

[Ted1]

Another method:

Thinking further about your problem, if you wish to show negative times in an hh:mm format, I think it's possible to "fake" a correct result using the CONCATENATE, MID, TEXT AND INT functions.

Let's assume that cell F5 shows the equivalent negative numeric time value (negative hours as whole numbers and minutes as a decimal of an hour after you've subtracted the standard 8 hours), let's say F5 = -3.0833 (ie. the staff memebr worked 4 hours 55 mnutes).

The following cell formula will give you the deficiency in worked hours and minutes (ie. -03:05 ). This will actually be in a text format but it will look like its negative hours and minutes' time value equivalent:

=CONCATENATE(MID(TEXT(F5,"00.00"),1,3),":",MID(TEXT((F5-INT(F5+0.99))*0.6,".00"),3,2))

As an explanation of the steps involved, I've:

1) Isolated the -3 simply by converting it to text and using mid(text) with a format which Excel requires to be specified.

2) Isolated the decimalised minutes value by subtracting the integer value of the whole hours. Because the Excel integer function takes the integer to next highest whole value (a negative one in this case), it would opt for -4 unless we add 0.99. In this step we've now isolated .0833 which is the decimal hours' equivalent of 5 minutes. It (.0833) now needs to be multiplied by 0.6 in order to be converted to minutes. Having done that the mid(text) function targets "05" as two character values beginning with the third character in the derived string.

3) The CONCATENATE function now simply places the two derived pieces of text into the one cell and separates them with a colon ":"

Ted1

PS. If you're an OZZIE, I haven't quite been up all night on this, I'm replying from Canada.

 

[byundt]

Here's a simpler worksheet formula using the TEXT function. It displays negative times as a string using -[h]:mm format

=IF(A1<A2,TEXT(ABS(A1-A2),"-" & "[h]:mm"),A1-A2)

Brad

 

[OOzy]

Guys/Gals,

Thank you for your help here is what I did:

cell E (which is the diff btw the 2 dates) has

=((D3-C3)*24)-8

Cell F has

=IF(E3<0,TEXT(ABS(E3/24),"-" & "[hh]:mm"),TEXT(ABS(E3/24), "[hh]:mm"))

Thank you

 

[AnotherHiggins]

Here's an idea that will allow you to keep all of your results formatted as times. This will have the advantage of allowing you to perform arithmetic with the results column later on.

Example Data:[tt]
A B C D
In Out day Lost Time
8:04 15:10 8:00 [COLOR=blue white]=IF(C2-(B2-A2)<0,(C2-(B2-A2))*-1,C2-(B2-A2))[/color]
7:11 14:00 8:00 [COLOR=blue white]=IF(C3-(B3-A3)<0,(C3-(B3-A3))*-1,C3-(B3-A3))[/color]
7:55 17:00 8:00 [COLOR=blue white]=IF(C4-(B4-A4)<0,(C4-(B4-A4))*-1,C4-(B4-A4))[/color][/tt]


Note that I've added a column in the example. Alternatively, you could use .33333 in the formula any time you see C?, if you don't want to add the column.

Now all "Lost Time" will show as an actual time. So how do we distinguish positive from negative times? With a conditional format, that's how!

Highlight the "Lost Time" column - in the example column D. Go to Format > Conditional Formatting, change the first box to Formula is, then in the second box type in [COLOR=blue white]=C1-(B1-A1)<0[/color]*. Now click on the Format button and change the font to [red]RED[/red]. Now all negative times will show as red.

Note: This idea does allow you to keep all results as actual numbers - better in my opinion than having text stings - but adding "positives" and "negatives" together won't work correctly since all times are actually considered positive.

*Again, if you choose to not add a column, you can replace C1 in the formula with .33333.

[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

OOzy,

I noticed in your profile that you've posted nearly 100 questions on Tek-Tips and have not given a single star. As contributors to this site, we come to expect people who are just 'passing through' - people who get help but don't know 'the rules' - but as you have been a member for nearly 5 years I just thought something needed to be said.

I know it isn't much, but remember that those little purple stars are the only 'payment' people get for helping you. None of us work for Tek-Tips, we are all just geeks that like helping others.

NOTE: Please do [red]not[/red] give me a star for this thread. Asking for stars is forbidden here. I am not trying to fish for stars or thank-you's. Again, I just thought something should be said. I hope this post doesn't offend anyone.

[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.