How to display negative times
 

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!

How to display negative times
 

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)

"Josh W" wrote in message
...
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!

How to display negative times
 

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

"Bob Phillips" wrote:

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)

"Josh W" wrote in message
...
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!

How to display negative times
 

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...

In article ,
Josh W wrote:

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

How to display negative times
 

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.

"JE McGimpsey" wrote:

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...

In article ,
Josh W wrote:

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

How to display negative times
 

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



"Josh W" wrote in message
...
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.

"JE McGimpsey" wrote:

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...

In article ,
Josh W wrote:

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

How to display negative times
 

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.

"Peo Sjoblom" wrote:

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



"Josh W" wrote in message
...
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.

"JE McGimpsey" wrote:

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...

In article ,
Josh W wrote:

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

How to display negative times
 

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


--
Regards,

Peo Sjoblom



"Josh W" wrote in message
...
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.

"Peo Sjoblom" wrote:

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



"Josh W" wrote in message
...
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.

"JE McGimpsey" wrote:

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...

In article ,
Josh W wrote:

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