subtracting time
 

Hi,
I am creating a spreadsheet to track the number of hours I work in a given
day then adding them up for the month.
Column A is my regular hours, column B I type in the actual hours I came in
and left that day, column C is the difference between my regular 8 hour shift
and the actual time I worked. I get into a problem when I get into negative
values. For example I am suppose to work 8 hours a day but if I leave early
or come in late by one hour I want it to show -1 under column C. The next
day I will come in one hour early and stay one hour late. So for the next
day column C will show 2. At the end of the month my net hours over/under
will be 1.

Regual Actual Time Over/Under
8:30 AM
4:30 PM

Total Over/Under = xxxx

I can't get the negative values to show right.

Thanks

subtracting time
 

If you change to the 1904 date system (toolsOptionsCalculation, check the
1904 date system checkbox), then negative time shows correctly.



--
---
HTH

Bob


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



"Ivano" wrote in message
...
Hi,
I am creating a spreadsheet to track the number of hours I work in a given
day then adding them up for the month.
Column A is my regular hours, column B I type in the actual hours I came
in
and left that day, column C is the difference between my regular 8 hour
shift
and the actual time I worked. I get into a problem when I get into
negative
values. For example I am suppose to work 8 hours a day but if I leave
early
or come in late by one hour I want it to show -1 under column C. The next
day I will come in one hour early and stay one hour late. So for the next
day column C will show 2. At the end of the month my net hours over/under
will be 1.

Regual Actual Time Over/Under
8:30 AM
4:30 PM

Total Over/Under = xxxx

I can't get the negative values to show right.

Thanks

subtracting time
 

that worked, thanks Bob.
Now that i have made this global excel change which affects every spread
sheet I work on if i have to work on other spreadsheets which happen to have
date/time would it affect them and change the results possibly giving
erroneous results?

"Bob Phillips" wrote:

If you change to the 1904 date system (toolsOptionsCalculation, check the
1904 date system checkbox), then negative time shows correctly.



--
---
HTH

Bob


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



"Ivano" wrote in message
...
Hi,
I am creating a spreadsheet to track the number of hours I work in a given
day then adding them up for the month.
Column A is my regular hours, column B I type in the actual hours I came
in
and left that day, column C is the difference between my regular 8 hour
shift
and the actual time I worked. I get into a problem when I get into
negative
values. For example I am suppose to work 8 hours a day but if I leave
early
or come in late by one hour I want it to show -1 under column C. The next
day I will come in one hour early and stay one hour late. So for the next
day column C will show 2. At the end of the month my net hours over/under
will be 1.

Regual Actual Time Over/Under
8:30 AM
4:30 PM

Total Over/Under = xxxx

I can't get the negative values to show right.

Thanks

subtracting time
 

A2: Time In
B2: Time Out
C2: Actual time =(B2-A2)*24
D2: Time Over/Under =C2-8

No need to change to 1904 date system


"Ivano" wrote:

Hi,
I am creating a spreadsheet to track the number of hours I work in a given
day then adding them up for the month.
Column A is my regular hours, column B I type in the actual hours I came in
and left that day, column C is the difference between my regular 8 hour shift
and the actual time I worked. I get into a problem when I get into negative
values. For example I am suppose to work 8 hours a day but if I leave early
or come in late by one hour I want it to show -1 under column C. The next
day I will come in one hour early and stay one hour late. So for the next
day column C will show 2. At the end of the month my net hours over/under
will be 1.

Regual Actual Time Over/Under
8:30 AM
4:30 PM

Total Over/Under = xxxx

I can't get the negative values to show right.

Thanks

subtracting time
 

The underlying date number is 4 years later, so any existing dates will show
4 years on. But it is workbook specific, not Excel wide, so doesn't affect
existing workbooks.


--
---
HTH

Bob


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



"Ivano" wrote in message
...
that worked, thanks Bob.
Now that i have made this global excel change which affects every spread
sheet I work on if i have to work on other spreadsheets which happen to
have
date/time would it affect them and change the results possibly giving
erroneous results?

"Bob Phillips" wrote:

If you change to the 1904 date system (toolsOptionsCalculation, check
the
1904 date system checkbox), then negative time shows correctly.



--
---
HTH

Bob


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



"Ivano" wrote in message
...
Hi,
I am creating a spreadsheet to track the number of hours I work in a
given
day then adding them up for the month.
Column A is my regular hours, column B I type in the actual hours I
came
in
and left that day, column C is the difference between my regular 8 hour
shift
and the actual time I worked. I get into a problem when I get into
negative
values. For example I am suppose to work 8 hours a day but if I leave
early
or come in late by one hour I want it to show -1 under column C. The
next
day I will come in one hour early and stay one hour late. So for the
next
day column C will show 2. At the end of the month my net hours
over/under
will be 1.

Regual Actual Time Over/Under
8:30 AM
4:30 PM

Total Over/Under = xxxx

I can't get the negative values to show right.

Thanks