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

 

[Boltie]

I've never really had much experience playing around with time calculations, so when i was browsing the questions and saw this one I thought I'd latch on and use it as a bit of a learning experience. I'm sure I'll use some of it at some point in the future.

 

[Boltie]

I was going to post this yesterday, but with the nice weather I decided a nice deserved drink out was required.

I have put the below together. You enter your start date & time, along with the estimated days, hours and minutes you think your project will take to complete. It will then output the end date & time.

I have saved the workbook with days set to 7, hours 0 and minutes 0 (168 hours). You can override individual 15 minute time slots if your working pattern changes. This will adjust the end date & time.

To adjust your working week pattern (and dinner breaks), amend the times inside the "% of Day Worked" formulas. There are a lot of calculations to do when adjusting all the formulas, but once you have it setup you can just update it as the project progresses and without any major recalculations.

There are validation rules for the days, hours and minutes fields.

Again, it's a bit crude and I'm sure this can be tidied up or done an easier way. I couldn't get the VLOOKUP or INDEX-MATCH functions to find an exact match even though there is one. I will have to have another play around later to see if I can fix that or maybe someone else here on the forums can help us out.

https://www.dropbox.com/s/nj9a779srkpv9gh/Work out project end date and time.xlsx

I'm not sure on the rules for linking to files but the FAQ just states you can't attach them. If this breaks the rules, I apologise.

 

[Boltie]

https://www.dropbox.com/s/nj9a779srkpv9gh/Work out project end date and time.xlsx

I had to quickly correct something.

*EDIT* I've just realised the links are the same, so either will work.

 

[Legacy 171839]

Ah, that looks great - thank you very much..!

I am having some trouble getting it to work though. So when I enter start date as today (15/07/2013) at 0800, with a duration of 1 day, it is returning an end date of 18/07/2013 at 11:00. I'm struggling to identify how it's coming to that conclusion?

 

[Boltie]

Hi frierpie,

I'll take another look when I get home. If it isn't working, hopefully it'll be a quick fix.

 

[Boltie]

I've just got back. I should have realised when you posted that 1 day = 24 hours. The end date & time is the 18/07/2013 11:00 because that's when 24 hours will be complete in terms of you working 8AM-4PM with a dinner at 12PM for 1 hour.