G
gavin
Sorry for the vague subject line but I couldn't think how to summarise this
problem in a few words.
I've created a workbook to record how many hours I spend working on various
projects. The workbook consists of a sheet for every week of the year and a
totals sheet. The weekly sheets feed into the totals sheet via SUMIF
formulas. The projects all have a unique code which are listed on the totals
sheets and are entered on each week sheet if I work on that project in that
particular week.
On the totals sheet I have a column to enter how many hours are allocated to
each project and a column which compares this number to the total hours
worked on that project. This is where the anomaly lies.
I have entered some test data. I have listed 5 project codes on the totals
sheet and in week 1 I've entered those 5 codes and have allocated 2 hours to
each project. The totals sheet is showing a total of 2 hours against of the
projects - so far so good.
If I enter "3:00" into the "Hours allocated" column for each row my "Hours
remaining" column shows "1:00" against each project. Similarly, if I
allocate 1 hour then the formula in the "Hours remaining column shows "Job
over by 1 hours". Perfect.
The problem arises when the hours allocated and the hours worked are the
same. In this example when I enter "2:00" into the "Hours allocated" column
some of the rows show "0:00" (which is what I would expect" ) but some show
"Job over by 0:00 hours"! I have checked and double checked and all the
relevant cells have the same formulas and are formatted the same - what
could be wrong?
Just to test, in a spare column I entered the formula =IF(C3-F3, "yes",
"no") - column C is Hour allocated and column F totals data from the weekly
sheets. The rows which show "0:00" return "yes" to this formula and the rows
which show "Job over by 0:00 hours" return "no".
I really don't have a clue how to fix this so any help would be greatly
received.
Regards,
Gavin
problem in a few words.
I've created a workbook to record how many hours I spend working on various
projects. The workbook consists of a sheet for every week of the year and a
totals sheet. The weekly sheets feed into the totals sheet via SUMIF
formulas. The projects all have a unique code which are listed on the totals
sheets and are entered on each week sheet if I work on that project in that
particular week.
On the totals sheet I have a column to enter how many hours are allocated to
each project and a column which compares this number to the total hours
worked on that project. This is where the anomaly lies.
I have entered some test data. I have listed 5 project codes on the totals
sheet and in week 1 I've entered those 5 codes and have allocated 2 hours to
each project. The totals sheet is showing a total of 2 hours against of the
projects - so far so good.
If I enter "3:00" into the "Hours allocated" column for each row my "Hours
remaining" column shows "1:00" against each project. Similarly, if I
allocate 1 hour then the formula in the "Hours remaining column shows "Job
over by 1 hours". Perfect.
The problem arises when the hours allocated and the hours worked are the
same. In this example when I enter "2:00" into the "Hours allocated" column
some of the rows show "0:00" (which is what I would expect" ) but some show
"Job over by 0:00 hours"! I have checked and double checked and all the
relevant cells have the same formulas and are formatted the same - what
could be wrong?
Just to test, in a spare column I entered the formula =IF(C3-F3, "yes",
"no") - column C is Hour allocated and column F totals data from the weekly
sheets. The rows which show "0:00" return "yes" to this formula and the rows
which show "Job over by 0:00 hours" return "no".
I really don't have a clue how to fix this so any help would be greatly
received.
Regards,
Gavin