Negative times

[gavin]

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]

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

 

[Bob Phillips]

Try using something like

=IF(sum_hours>144,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)

 

[gavin]

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]

Try using something like

=IF(sum_hours>144,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

 

[Dave Peterson]

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.

 

[JE McGimpsey]

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.

 

[gavin]

Can anyone help me with the query below?


Regards,



Gavin

 

[redbelly]

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