Work out project end date and time

[Legacy 171839]

Hi,

Once again I call upon those with skills > mine, who never fail to disappoint.

So, cell A1 has date time 04/03/2013 08:00, which is when I will begin a new project/piece of work.

I estimate that piece of work will take 168 hours to complete.

If I enter 168:00 in B1, then =A1+B1 in C1, it tells me the job will be complete on 11/03/2013 08:00.

Now, the obvious flaw here is I don't work 24/7 (as much as my boss wished I did I'm sure).

I'd like a calculation that takes into account I work a 7 hour day, Monday to Friday (0800 - 1600 with 1 hr lunch), so the calculation should have the above piece finishing at 04/04/2013 16:00.

My preference is not to use vba, though if it's the only solution I'll gladly take it.

Thanks in advance for your help

 

[Special-K99]

Does this work?

Add working hours only to a date and time

 

[Legacy 171839]

It does to an extent thanks, yes.

What if however, I did work 1 saturday, or a half day one wednesday? Anyone aware of a formula with a variable like that that could be tinkered with?

 

[Boltie]

There are probably many ways to go about this but I quickly put the below together. It's not complete as it's not dealing with the hours exactly but in terms of days it should be a good enough estimate. Probably worth others checking it though as it's the end of the day.

CELL A1 = Start Date = 04/03/2013 (date format)
CELL B1 = Predicted time (hours) to complete job = 168.00 (number or general format)
CELL C1 = End Date = "=A1+((B1/7)-NETWORKDAYS(A1,A1+(B1/7))+(B1/7))-D1" (date format)
CELL D1 = Extra Days such as the number of weekend days you will work (number or general format)

My result was 02/04/2013

 

[Boltie]

Scrap the above. I was rushing before leaving and really didn't think that one out.

 

[Boltie]

OK, again this doesn't deal with exact time, but it will give you the end date.

A1 = "=SUM($B$1:B1)"
B1 = "=IF(OR(WEEKDAY(C1)=1,WEEKDAY(C1)=7),0,1)"
C1 = Your start date
C2 = "=C1+1"
D1 = The amount of hours
E1 = "=VLOOKUP(D1/7,A:C,3,FALSE)-F1"
F1 = Weekend days worked

Then expand columns A,B and C (from cell C2) down for as many days required. Row 365 would seem enough to me.

*EDIT*

Modifying the above should be easy enough to include the time, I might come back to it later.

 

[Legacy 171839]

Thanks for your help Boltie, But I'm getting N/A from the VLOOKUP. What's the "D1/7" bit all about? Never seen that before?

 

[Legacy 171839]

It works when I remove the "/7", and also if I enter the estimated time in days, not hours.

 

[Boltie]

The D1/7 was to work out from the hours how many working days it would be. It should have been /6 really as there is the 1 hour dinner to account for.

I went back to this last night for about an hour and did develop it to include the time (working to periods of 15 minutes). I got it working well up to a certain point. I'll explain the problems later and upload my worksheet when I get home if you like?

The above solution is quite crude really. I'm not sure why you're getting N/A.

 

[Legacy 171839]

Sheesh, thanks for all the effort - appreciated... hope you're able to use the outcome yourself for something at least...