I've been trying to put together a timesheet to record the hours that my
colleagues work. It consists of 13 4 week sheets to give a full year of
records.

The base time for a 4 week block is 144 (yeah, I know we got it easy!!!) but
it is permitted to work fewer hours than that and carry over a debit and to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be greatly
appreciated.

I have two cells which both compare actual time worked with the base time.
If the balance is positive I get a number in one cell (X hours credit) and
if the balance is negative I get another number in the other cell (X hours
debit - but this is still a positive number). I have been trying to get
whichever cell has a figure in it to feed into a cell on the ensuing sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I would
rather have just one cell showing either a negative or positive time and
then have this feed into the next sheet.


Thanks in advance,


Gavin

Could you give an example of the formulae that you are using?

"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that my
colleagues work. It consists of 13 4 week sheets to give a full year of
records.

The base time for a 4 week block is 144 (yeah, I know we got it easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be greatly
appreciated.

I have two cells which both compare actual time worked with the base time.
If the balance is positive I get a number in one cell (X hours credit) and
if the balance is negative I get another number in the other cell (X hours
debit - but this is still a positive number). I have been trying to get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I would
rather have just one cell showing either a negative or positive time and
then have this feed into the next sheet.


Thanks in advance,


Gavin

Try using something like

=IF(sum_hours144,sum_hours-144,0)
for the positive total, and

=IF(sum_hours<144,144-sum_hours,0)
for the negative total.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bigwheel" <bigwheel at lineone dot net wrote in message
...
Could you give an example of the formulae that you are using?

"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that my
colleagues work. It consists of 13 4 week sheets to give a full year of
records.

The base time for a 4 week block is 144 (yeah, I know we got it easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be
greatly
appreciated.

I have two cells which both compare actual time worked with the base
time.
If the balance is positive I get a number in one cell (X hours credit)
and
if the balance is negative I get another number in the other cell (X
hours
debit - but this is still a positive number). I have been trying to get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I
would
rather have just one cell showing either a negative or positive time and
then have this feed into the next sheet.


Thanks in advance,


Gavin

"bigwheel" <bigwheel at lineone dot net wrote in message
...
Could you give an example of the formulae that you are using?

I've got =IF(C31(C2*4),C31-(C2*4),"") in the "credit" cell and
=IF(C31<(C2*4),(C2*4)-C31,"") in the "debit" cell - where C31 has the total
number of hours worked in the 4 week period and C2 simply contains "36:00"
(i.e. the hours worked in a flat week).



Gavin






"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that my
colleagues work. It consists of 13 4 week sheets to give a full year of
records.

The base time for a 4 week block is 144 (yeah, I know we got it easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be
greatly
appreciated.

I have two cells which both compare actual time worked with the base
time.
If the balance is positive I get a number in one cell (X hours credit)
and
if the balance is negative I get another number in the other cell (X
hours
debit - but this is still a positive number). I have been trying to get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I
would
rather have just one cell showing either a negative or positive time and
then have this feed into the next sheet.


Thanks in advance,


Gavin

"Bob Phillips" wrote in message
...
Try using something like

=IF(sum_hours144,sum_hours-144,0)
for the positive total, and

=IF(sum_hours<144,144-sum_hours,0)
for the negative total.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Hi Bob,
I ahven't tried this but doesn't a negative time in Excel simply cause
"##########" to be displayed - that's what I've found anyway?


Regards,


Gavin





"bigwheel" <bigwheel at lineone dot net wrote in message
...
Could you give an example of the formulae that you are using?

"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that
my
colleagues work. It consists of 13 4 week sheets to give a full year
of
records.

The base time for a 4 week block is 144 (yeah, I know we got it
easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit
and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be
greatly
appreciated.

I have two cells which both compare actual time worked with the base
time.
If the balance is positive I get a number in one cell (X hours credit)
and
if the balance is negative I get another number in the other cell (X
hours
debit - but this is still a positive number). I have been trying to
get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I
would
rather have just one cell showing either a negative or positive time
and
then have this feed into the next sheet.


Thanks in advance,


Gavin

If you format that cell as general, you'll see a negative number.

You can also change your base date from 1900 to 1904
(tools|options|calculation tab|check 1904 date system)
to see negative times (and dates).

Be aware that any dates in that workbook will be off by 4 years and one day.

And copying dates between different workbooks with different base dates also
causes that problem.

gavin wrote:

"Bob Phillips" wrote in message
...
Try using something like

=IF(sum_hours144,sum_hours-144,0)
for the positive total, and

=IF(sum_hours<144,144-sum_hours,0)
for the negative total.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi Bob,
I ahven't tried this but doesn't a negative time in Excel simply cause
"##########" to be displayed - that's what I've found anyway?

Regards,

Gavin


"bigwheel" <bigwheel at lineone dot net wrote in message
...
Could you give an example of the formulae that you are using?

"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that
my
colleagues work. It consists of 13 4 week sheets to give a full year
of
records.

The base time for a 4 week block is 144 (yeah, I know we got it
easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit
and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be
greatly
appreciated.

I have two cells which both compare actual time worked with the base
time.
If the balance is positive I get a number in one cell (X hours credit)
and
if the balance is negative I get another number in the other cell (X
hours
debit - but this is still a positive number). I have been trying to
get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I
would
rather have just one cell showing either a negative or positive time
and
then have this feed into the next sheet.


Thanks in advance,


Gavin







--

Dave Peterson

You can display negative times in the 1904 date system
(Tools/Options/Calculation, check the "1904 date system" checkbox).

If you have dates in your worksheet, they'll all be shifted by 4 years
and one day (times aren't affected), but subsequent date entries will be
handled correctly.


In article ,
"gavin" wrote:

I ahven't tried this but doesn't a negative time in Excel simply cause
"##########" to be displayed - that's what I've found anyway?

Can anyone help me with the query below?


Regards,



Gavin


"gavin" wrote in message
...
I've been trying to put together a timesheet to record the hours that my
colleagues work. It consists of 13 4 week sheets to give a full year of
records.

The base time for a 4 week block is 144 (yeah, I know we got it easy!!!)
but
it is permitted to work fewer hours than that and carry over a debit and
to
make up that time in the next block.

I am having real problems trying to manipulate negative times and to
therefore get the carry over working - any help with this would be greatly
appreciated.

I have two cells which both compare actual time worked with the base time.
If the balance is positive I get a number in one cell (X hours credit) and
if the balance is negative I get another number in the other cell (X hours
debit - but this is still a positive number). I have been trying to get
whichever cell has a figure in it to feed into a cell on the ensuing
sheet -
but if it is a debit figure I can't get it to subtract.

I think I am probably going about this the wrong. I guess ideally I would
rather have just one cell showing either a negative or positive time and
then have this feed into the next sheet.


Thanks in advance,


Gavin

Hello Gavin,

If I understand you, the hours worked are entered in hours:minutes
format, eg. 7:00 is entered if 7 hours are worked on a particular day.

This formula should work for you:

=24*SUM(A10:A30)-144

(But instead of A10:A30, use whatever range has the daily hours
worked.)

YOU ALSO NEED TO FORMAT THE CELL to something other than a Date or Time
format. I recommend using the Number format, with 2 or 3 decimal
places.

The factor of 24 is needed because when you enter numbers in
hours:minutes format, Excel internally converts it to a fraction of a
day. For example, if somebody works 6 hours one day, and you enter
6:00, Excel will consider the number to be 0.25 (6 hours is 0.25 days),
EVEN THOUGH it is displaying it as "6:00".

Hope this helps,

Mark