Time difference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephanie - 20 Mar 2006 20:37 GMT

This is time worksheet:

Time in/time out/Number hours/difference.

First three are fine. The difference should be the difference between
column C and 8 hours.

Ideally, it would show the difference in hours and minutes. I've tried
=C2-8, but all I get is #######.

Any suggestions?

Signature

Steph

Reply to this Message

Sloth - 20 Mar 2006 20:57 GMT

excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use
this formula

=C2-8/24
or
=C2-1/3

Other notes about time:
-I like to keep it as 8/24, so it makes more sense to other people who might
look at it, but it really doesn't matter which format you use.
-If you want something in decimal hours (5.5 instead 5:30) mulitply the
result by 24 and format as a number.
-If you want elapsed time that will be over 24 hours use a custom number
format of
[h]:mm
without the brackets, 27:34 would show like 3:34.

> This is time worksheet:
>
[quoted text clipped - 7 lines]
>
> Any suggestions?

Reply to this Message

Stephanie - 20 Mar 2006 21:56 GMT

This is what I put, but it's not working.

=E2-8/24 h:mm
=E2-1/3 h:mm doesn't work either.

Signature

Steph

> excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use
> this formula
[quoted text clipped - 24 lines]
> >
> > Any suggestions?

Reply to this Message

Stephanie - 20 Mar 2006 22:14 GMT

ok, I figured it out. It shows negative numbers as #####. Is there any way
to make it show as a negative?

Signature

Steph

> excel stores time in number of days, to 8 hours is really 8/24, or 1/3. Use
> this formula
[quoted text clipped - 24 lines]
> >
> > Any suggestions?

Reply to this Message

kassie - 21 Mar 2006 20:37 GMT

You could use an extra column, so that you have one column for positive, and
one for negative, but both showing positive. To do that, merely swop the
arguments, as follows:

=IF(C4<1/3,(8/24)-C4,"") to calculate negative hours and
=IF(C4>1/3,C4-(8/24),"") to calculate positive hours. In this way, you can
balance out overtime and time off.

> ok, I figured it out. It shows negative numbers as #####. Is there any way
> to make it show as a negative?
[quoted text clipped - 27 lines]
> > >
> > > Any suggestions?

Reply to this Message

Sloth - 21 Mar 2006 23:06 GMT

Excel can't handle negative dates or times. There are a couple ways you can
handle it...

I. Typically people avoid negative time by using a formula like this

=MAX(C1-8,0)

this way if "Number of Hours" is below 8 then there is no overtime.

II. If you need negative time then you can use a format like this

h:mm;-#/24
or
h:mm;-General

the first output will round the number to the nearest hour (example: -3:35
will show as -4/24). The second will show the actuall number (example: -3:35
will show as -0.14931). Note, the value of the cell is not rounded in
either, only the display is rounded (ie. the cell will show -4/24, but will
equal -0.14931 if used in another cell's formula).

III. If you need negative time in time format (for asthetic reasons) you
can use this formula, but it can't be used in other formulas.

=IF(C1<1/3,"-","")&TEXT(ABS(C1-1/3),"h:mm")

IV. I am sure there are other options, but all of them will have some sort
of drawback depending on what you want to do with the result.

> ok, I figured it out. It shows negative numbers as #####. Is there any way
> to make it show as a negative?
[quoted text clipped - 27 lines]
> > >
> > > Any suggestions?

Reply to this Message