Hi, file attached.


stumped on why total is just 15 mins
using sum(g4:g41) method or just a+b+c etc.


I wanted to have the credit debit figure add them up and subtract from the total to see time remaining.


Can someone sort this out please as the time its taking me to create it to see how close to failure I will be given the time I have left to do it, means ironically I wont manage to get the job done due to time spent instead trying to get the excel spread sheet made , as I am counting hrs now, stop and I fail !!


I just need a fixed chart now, no time left for fiddling.



DBenz

Attached Files

Task time estimator and progress credit debit.xlsx (15.6 KB, 13 views)

[... deleted ...]

[Sorry about the previous misdirection.]

As I explained, the format for C40 (at least) should be Custom [hh]:mm:ss.

The specifier "[hh]" will display hours greater than 23.

But note that C40 displays 576:15:00 instead of 48:15:00, as you might expect.

That's because some of the "times" in C2:C39 exceed 24 hours.

So format C2:C39 as Custom [hh]:mm:ss as well.

-----

But really, I suspect the data in C2:C39 is not entirely correct.

For example, C2 8 days plus 8 hours (8:00:00). But B2 is simply 8 (hours?). So I suspect the 8 days is incorrect.

Similarly with C3. But C4 is correctly just 1 hour (1:00:00), and B4 is 1.

So I suspect the root cause of the problem is an inconsistency in converting column B (decimal hours) into column C (Excel time).

Enter the following formula into C2 and copy down through C39:

=B2/24

Again, format C2:C40 (even C41) as [hh]:mm:ss, even if hh:mm:ss should suffice. The format [hh] will permit you to have 24 or more hours in column B.

Attached is your sample that I revised. Custom format your time into [h]:mm:ss. There's a formula in column D (my own insertion). Take note also of the SUBTOTAL function that was used to get the total of the rows. In this way, any rows of data that you insert or any subtotal will be automatically included in your grand totals.


In cell X2 where you want the grand total of hours clocked in for a specific task, SUMIF was used so that if you insert columns, the total hours that you inserted will also be added in the sum. If you don't like the column heading "Hours of Work, you may change them, but make sure that you edit the formula in cell X2. Replace the "Hours of Work" to your desired heading.


About the debit or credit column. If the estimated number of hours is greater than the actual hours worked over the task, is the result credit? Your formula of =C2-X2 might not work when there is slippage, when the actual work hours exceeds the estimated hours. I separated the credit and debit columns to take into account the possibility for slippages, credit for positive results, debit for negative ones. It's your choice.

Attached Files

Copy of Task time estimator and progress credit debit.xlsx (17.6 KB, 14 views)