Hi,



My initial column was number, so 8 2 3 0.5 0.25 etc for hours I estimate tasks of the job will last.
then having created start and end times columns, several for each task, they obviously generate hh:mm:ss type results when deducting start time from end time.
I create a new column beside the initial numbers of hours per task, and populate it manually so 8 hrs is now 08:00:00, 0.25 is 00:15:00 etc


number column added to 48.25 hrs, but this new column , using =C2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14


adds up to 23:45


Why ?


I then use autosum on the first 19 rows and success, include a further 6 and failure, tackle the last few rows and success, do it in three chunks 19, 6 and 14 rows and success for each, its as if autosum bottles out of more than 19 ? One would think the format was corrupt for the 6 rows, but on their own they behave.


I have totals of 20:15 07:00:00 and 21:00:00 but adding then these together using a+b+c method I get 00:15:00 so that fails.


I could post the file but cant see a way of doing so.


I simply want rows as tasks, a column for how long I estimate each task to take, then start and end time cells for the times I start and end each task, and several per task to allow for human breaks ! then a column that subtracts the starts from the ends and adds them up for a total, then beside that a column deducting that from the estimate to give a 'credit' or 'debit' figure so I can see if to panic or relax !





anyone care to create such, sounds simple.


DBenz

You can attach a file by using the GO ADVANCED button.

Better: =SUM(C2:C14)

Use the Custom format [h]:mm:ss or [hh]:mm:ss .

Then it will display 48:15:00, which is the actual value of the sum.

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, 11 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, 12 views)

Hi , thanks all,
Marcia, to have this so I can fill it in is great.


yes, if estimated hrs is more than actual its a credit.


Attached is it with your formula now taken down to populate all the task rows.


I would like that the hours of work only display a figure if there is a start and end time preceding that column.



Something is wrong though, I have credits when tasks not even performed yet, so if I add them all up and deduct from grand total it makes it look extremely healthy !!!
Can you fix that for me ?


In fact those blank rows can be nuked. Original design was sheet 2, they were when I was sussing additions in three chunks.


I wanted it that the credits and debits are then introduced into the grand total to show me the new expected total time it all takes, as I progress.


Is that just a case of grand total -(credit total) +(debit total) cells ?
or will it require different formula ?


I made a task a deliberate credit but the grand total didnt alter.


I still have that wrong 00:15:00 in red fill. so cant use that figure.


DBenz

Attached Files

Task time estimator and progress credit debit forum version developed F2.xlsx (21.2 KB, 9 views)

Because you didn't pay attention to my previous explanation. I'm done with you.

Hi,
Joeu2004
I see only 8 hrs as most

I don’t understand, I click C2 and its not saying B2+ 08:00:00

BI manually typed the hh:mm:ss into C to represent hrs for the hours in B
B2 is 8 hrs, you say 8 days.

You lost me I’m afraid.


Marcia, I see I have a column sorting out the 00:15:00 thanks.


so edit that out of my response


Dbenz

Something is wrong though, I have credits when tasks not even performed yet, so if I add them all up and deduct from grand total it makes it look extremely healthy !!!
Can you fix that for me ? - I added another IF in the formula under credit and debit columns to return zero (not blank) if there is no data to compute. copy the formula down to the next set of tasks.
I wanted it that the credits and debits are then introduced into the grand total to show me the new expected total time it all takes, as I progress. - Grand Totals of estimated hours plus grand totals of credit hours minus grand totals of debit hours or =d45+y45-z45
I suggest you feed your data in a Ghantt Chart to get a better picture of the progress of your on going project.

Attached Files

Copy of Task time estimator and progress credit debit forum version developed F2.xlsx (26.0 KB, 7 views)

Hi Marcia,
many many thanks,
I have filled in task 5 but the net credit debit then goes ########


How do I get the cell for days @ 11hrs a day work to display days, I try for grand total after creditdebit/11 but cannot display a day figure.


Not aware of a Ghantt chart, sounds useful so when time allows I will explore, no time at the mo, as this excel chart is telling me, I am in need of a day I dont have :-(


DBenz

Attached Files

Copy of Task time estimator and progress credit debit forum version developed F2.xlsx (21.8 KB, 7 views)

The net credit hours display ############# because the net result from task 1 to 5 is negative, meaning the workers clocked in more hours on the tasks than the total allowed hours of work.


To get the 11 hour days, you multiply your initial formula by 24, the total hours in a day.