Hours Calculations - Time left to go

[HAWM]

Hi All,

Currently working on a small project to calculate hours left on a project, which I'm finding hours a new struggle to work with in honesty. I'm an accountant and I like normal numbers!

The aim of the file is to plan project completion times - I've already built in to be affected by breaks.

So to use this we will go down and say today we are working on project 123 today and we know they take 16 hours to complete, so given the breaks when should we expect this to finish when? We will be going in to manually tell it the day we are working on and the project and subsequently it could be a few days gap before we come back to it if we don't finish it that day.

So what I'm looking for is when I get to the next day, how to I get it to check for the previous hours worked against this project 123 and tell me how many hours are left that I'll need to plan in for the next available day.

Any help would be fantastic - I've wasted enough hours on this already!

Thanks in Advance!

 

[jasonb75]

I'm an accountant and I like normal numbers!

Times are normal numbers, they just look different. For the purpose of formula calculations 12 hours = 0.5 days

Where is the initial total time coming from? Assuming that it is the result or a lookup to another table, something like this entered into Q2 and filled down should do the trick.

=IFERROR(AGGREGATE(15,6,T$1:T1/(D$1:D1=D2),1),lookup)

 

[HAWM]

Hi Jason,

It's more the formatting of the number as this will be used by people with limited skills and the numbers need to be fairly precise. SO they will know a project is 16hrs of 5 hours 45mins so they will enter 16:00 and 05:45 respectively. I might set this to a look up at someone once I can standardise the timings of projects. But for now I'm thinking for their logic if hours = zero then its either complete or never been done.

I've not used AGGREGATE before and didn't have much success here:

- Have I done something wrong?

 

[jasonb75]

That formula was for Q2 on the assumption that the original time of 16:00 was already the result of a formula, if the 16:00 is entered manually then the formula needs to include that row as well

=IFERROR(AGGREGATE(15,6,T$1:T2/(D$1:D2=D3),1),"Not found")

The 2 ranges in bold should always start at row 1 and end 1 row above the formula, the single cell in italics should be the same row as the formula.
Trying to mix such formulas in a column with constants (typed values instead of formulas) is probably going to make things difficult and it could get messy, it would be better to have only formulas in the total time column, with a simplified list of projects and total hours elsewhere, something like the example below.

Note that the mini-table in A7:B9 could be located in another sheet if preferred, I just put it at the bottom for easier reference.

Book1
	A	B	C	D													Q	R	S	T	U
1	Day	Date	Week	Works Order													Total Time	Start	Finish	Remain	Break
2	Thursday	18/06/2020	25	123													16:00:00	07:00	16:00	06:15	00:45
3				456													12:00:00	15:30	17:00	10:30
4				123													06:15:00	15:30	17:00	04:45
5
6
7	Works Order	Total Time
8	123	16:00
9	456	12:00
Sheet7

Cell Formulas
Range	Formula
A2	A2	=B2
C2	C2	=WEEKNUM(B2)
Q2:Q5	Q2	=IF(D2="","",IFERROR(AGGREGATE(15,6,T$1:T1/(D$1:D1=D2),1),VLOOKUP(D2,$A$8:$B$9,2,0)))
T2:T5	T2	=IF(COUNT(R2:S2)=2,Q2-(S2-R2+U2),"")

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	=$A$9