Hours - display minus time ?
Posted by Steve Evans on March 30, 2001 1:55 AM
Can anybody help ?
I have a timesheet spreadsheet which calculates the hours worked each day
and provides accumulative hours for the month and a balance whether credit
or debit against standard hours. My problem is in the first row. This
happens when there is a debit balance brought forward.
The accumulative figure after the first day will be all the hours worked
that day plus any credit hours brought forward from the previous month or
minus any debit balance. But when the debit balance is less than the hours
worked the result is not shown but ####### etc. E.g. minus balance brought
forward 7:24 and hours worked 7:00 therefore leaving a result of 24 minutes
in debit but it wont show.
Please help !!!!!!!!!!!!!!
Thanks
Posted by Dave Hawley on March 30, 2001 3:31 AM
Hi Steve
If you do not need the result for further calculations (unless with some other IF statement) you could use:
=IF(B1-A1<0,TEXT(A1-B1,"-hh:mm"),B1-A1)
Or you could display the result as a decimal:
=IF(B1-A1<0,VALUE(TEXT("-"&A1-B1,"general")),B1-A1)
Dave
OzGrid Business Applications
Posted by Steve Evans on March 30, 2001 6:07 AM
Dave
Thanks for the reply but I think I'm trying to over complicate things.
The formula I'm using in the cell where the result should be shown is - =IF(I10=" ",IF(J10=" "," ",IF(K10="c/l",IF(N5>0,N5,N6),IF(N5>0,SUM(J10+N5),SUM(J10-N6)))),IF(J10=" ",IF(N5>0,SUM(I10+N5),SUM(I10-N6)),IF(K10="c/l",IF(N5>0,SUM(I10+N5),SUM(I10-N6)),IF(N5>0,SUM(I10+J10+N5),SUM(I10+J10-N6)))))
Where I10 - Attendance for the day
J10 - Absence during the day either credit or debit ("c/l")
K10 - Reason for absence
N5 - Any credit brought forward from previous mnth
n6 - Any debit brought forward from previous mnth
Where would I put your suggested formula in that ?
Sorry to be a nuisance I do appreciateyour assistance.
Thanks again
Steve
