work out finish time, from start time, using working hours

Hi, I'm creating a production planning schedule. I'm attempting to have it so i input a start date and have excel calculate a finish time.
so here is where i become stuck. I can't seem to figure out how to set it up so it only counts working times as apposed to 24/7.

for example if i input a start time of 19/01/2016 10:00:00 and i add 30 hours, i would need it to return 22/01/2016 13:00:00. Not 20/01/2016 16:00:00. (providing I've worked that out correctly)

so working based around a 9 hour day 8 am to 5 pm Monday to Friday.

does anyone have any ideas, is it even possible?

I hope this is clear enough, it's my first post so i apologize if it isn't. please ask for more details if necessary.

Thank you.

Try something like this:

Please Login or Register  to view this content.

Where D4 is 19/01/2016 10:00:00 and E4 is 30:00

Stephen

First and foremost, thanks very much for taking the time to help me out.

This formula works as you said, but as i am pulling my info from another spread sheet i already have this formula (bellow), i could rearange my sheet to accommodate that formula. but can you think of a way to work your formula into mine. so i can test it on numbers i already have.

=G4+((F4*E4)*0.0006944444)

where G4 is date/time F4 number of units and E4 time in minutes per unit.

thanks,

Hayden

Hi Hayden,
What are the values showing in G4, F4 and E4 and what is the expected result?
Stephen

hi Stephen
g4 is "19/01/2016 10:00"
f4 is "200" (number of units to be made)
e4 is "38" (time in minutes to complete 1 unit)
expected result is what it should be "24/01/2016 16:40" which is 126 or so hours from 19/01/2016 10:00

but i need it to work within working hours, what I'm trying to achieve would be 08/02/2016 10:40. so 126:40 spread across Monday to Friday 8 am 5 pm

Hayden

Hi Hayden,

Try this...

Please Login or Register  to view this content.

Stephen, that's magic mate, your a wizard! It works on all the testing i have done.

I appreciate that so much, i feel bad asking a follow up question, but.... haha

So a huge over sight on my part, Friday is a 5 hour day, just to make things interesting. Would you have an idea to make it work for that.

My idea involves me forcing everyone to work the extra hours, but i don't want to get hurt.

Hayden

So you want to show 09/12/2016 13:40 instead of 08/02/2016 10:40?

yep, that's what i make it.

Hmm, it was too hard to get my head around so I built a macro instead.

Test.xlsm

Please Login or Register  to view this content.

That's brilliant Stephen.

This leaves me with another problem.... sorry

With the formula, I can more or less work out what is going on and tweak if necessary. for example no one really works for 9 hours a day. I can also copy it across to work on other rows, for example i would have it for each line next to an employees name.

With Macro's i'm pretty useless to be honest, the only ones I've ever managed to do is with the record macro function. I wouldn't even know how to make that macro work on other rows.

Could you offer up any helpful tips?

Kind regards,
Hayden

Hi Hayden,
The macro is basically using a number of formula's.
y is the number of hours that we need to add onto the start date (i.e. roughly 126 hours)
x is the start time
Then a loop runs that says If it is Mon-Thurs y will reduce by 9 hours a day x will increase by 9 hours (working) + 15 hours (non-working)
If it is Friday y will reduce by 5 hours and x will increase by 5 hours (working) + 67 hours (non-working incl. the weekend)
The loop will run until y is 0, so there is also an extra If statement to say that when we get below 9 or 5 hours then whatever time is remaining needs to be added to x and y should become 0.
Stephen

Ahh very clever. I can see some of it, but not all of it. Ill try and make some more sense of it.

Thank you Stephen