How to display negative times

[Guest]

Hi, each month I create an excel worksheet for the total amount of hours
attended at work. I have formatted the cells to show the start and finish
time in 13:00 format. I then subtract start from finish to get the total
hours for that day. In another cell I add up the total hours during 31 days
to get the month's total.

The problem is that I then have to subtract the total obligatory hours from
the total hours of actual attendence. If i have hours missing, only #########
symbols are displayed and not the actual amount of hours missed. This
totally destroys the whole point of the exercize. (If I format the cell to
"general" then the total is completly wrong).

How do I remedy this? I use Office 2003 with Windows XP. Would greatly
appreciate your help. Many thanks!

 

[Bob Phillips]

Change the date system to 1904, Tools|Options|Calculation, and check 1904
system date.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob. However, when I do this, I get the completely wrong result!!

 

[JE McGimpsey]

Unless you're just commenting for the heck of it, it would have been
helpful to indicate what your formula is, what the referenced cell
values are, and what you expected the result to be...

 

[Guest]

Wow! What an attack...I thought I had explained well in the beginning. In
the end I formatted each cell as [hh]mm and changed to 1904 date system and
it seems to be working.

 

[Peo Sjoblom]

What attack? You posted back to a suggested solution with no info except
that you got a completely wrong result. One would expect a description of
what was wrong with the result vis-à-vis the result you expected.


--
Regards,

Peo Sjoblom

Wow! What an attack...I thought I had explained well in the beginning. In
the end I formatted each cell as [hh]mm and changed to 1904 date system
and
it seems to be working.

Unless you're just commenting for the heck of it, it would have been
helpful to indicate what your formula is, what the referenced cell
values are, and what you expected the result to be...

 

[Guest]

Thanks. The trouble I had was that I have eg. cell C35 which displays the
amount of hours we MUST attend - formatted as numbers (i.e. 155.5) and cell
D34 which adds up the total amount of hours actually attended during the
month - formatted as hours (i.e. 165:30). Then cell C36 is supposed to
subtract C35 from D34 to give me the result of overtime of missing hours.
Trouble is that b/c C35 is formatted as numbers I cannot get the result. So
in the end I formatted the whole document as time which is inconvenient since
I have to display to my colleagues for example fifty and a half hours as
50:30 and not 50.5 which is normal.

What attack? You posted back to a suggested solution with no info except
that you got a completely wrong result. One would expect a description of
what was wrong with the result vis-à-vis the result you expected.


--
Regards,

Peo Sjoblom

Wow! What an attack...I thought I had explained well in the beginning. In
the end I formatted each cell as [hh]mm and changed to 1904 date system
and
it seems to be working.

Unless you're just commenting for the heck of it, it would have been
helpful to indicate what your formula is, what the referenced cell
values are, and what you expected the result to be...

Thanks Bob. However, when I do this, I get the completely wrong
result!!

 

[Peo Sjoblom]

If you multiply the time result with 24 and format as number or general you
will get decimal time


--
Regards,

Peo Sjoblom

Thanks. The trouble I had was that I have eg. cell C35 which displays the
amount of hours we MUST attend - formatted as numbers (i.e. 155.5) and
cell
D34 which adds up the total amount of hours actually attended during the
month - formatted as hours (i.e. 165:30). Then cell C36 is supposed to
subtract C35 from D34 to give me the result of overtime of missing hours.
Trouble is that b/c C35 is formatted as numbers I cannot get the result.
So
in the end I formatted the whole document as time which is inconvenient
since
I have to display to my colleagues for example fifty and a half hours as
50:30 and not 50.5 which is normal.

What attack? You posted back to a suggested solution with no info except
that you got a completely wrong result. One would expect a description of
what was wrong with the result vis-à-vis the result you expected.


--
Regards,

Peo Sjoblom

Wow! What an attack...I thought I had explained well in the beginning.
In
the end I formatted each cell as [hh]mm and changed to 1904 date system
and
it seems to be working.

:

Unless you're just commenting for the heck of it, it would have been
helpful to indicate what your formula is, what the referenced cell
values are, and what you expected the result to be...

Thanks Bob. However, when I do this, I get the completely wrong
result!!