A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours

On Thu, 26 Apr 2007 04:52:02 -0700, hema
wrote:


A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours




You have to either use the 1904 date system; or format the result cell as
something other than date/time, and perform the appropriate math to convert the
value (fraction of a day) into hours and minutes.
--ron

You could use 1904 date system (tools|Options|calculation tab)

But be aware that any date in that workbook will be off by 4 years and a day.

And exchanging data (including dates) between workbooks with different date
systems can be a pain.

hema wrote:

A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours



--

Dave Peterson

=(C2*24)-(D2*24)

"hema" wrote:


A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours

You can't do that but there is a workaround by displaying the negative time
in minutes. Try the formula:-

=(C2-D2)*24*60

"hema" wrote:


A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours

Hi Hema,

To check if difference is negative and display as a negative time if negative:
=IF(D2C2,"-"&TEXT(D2-C2,"H:MM"),TEXT(C2-D2,"H:MM"))

To sum a range (rows 2 through 100):
=IF(SUM(D2:D100)SUM(C2:C100),"-"&TEXT(SUM(D2:D100)-SUM(C2:C100),"H:MM"),TEXT(SUM(C2:C100)-SUM(D2:D100),"H:MM"))

"hema" wrote:


A B C
D E
1 In Time Out time Actual hours Working hours
2 9:00 17:00 8:00
8:45 =C2-D2

In the E2 cell i am getting only ####,here i want to get the negative hours