Work Schedule Dates and Times Problem

[Ciaran1986]

Hello

I am trying to create a work schedule based on dates and times using excel 2013.

A1 = Current Date and Time =NOW()

A4 = Task Name (text format)
B4 = Time to complete task (in hh:mm format)
C4 = Completion date and time (=A1+B4) in dd/mm/yyyy hh:mm format

This currently works ok, however the dates and times presume I am working 24 hours a day, 7 days a week. I would like C4 to work within my work hours (Monday - Friday, 8.00 - 16.30).

Example - If the current date and time is Friday 06/09/2013 15:00 and I insert a task that is going to take 4 hours {B4 = 04:00 (hh:mm)}, I would like the completion date and time (C4) result to be Monday 09/09/2013 10:30, (the 4 hour task will take from 15:00 - 16:30 on the Friday, then 8:00 - 10:30 on the Monday).

Similarly when I add in the next line (Task Name in A5 and Duration in B5) C5 will =C4+B5, again I would like C5 to work within my work hours.


Can anybody help me with this?

[Ciaran1986]

Does anyone know is it even possible to do what i'm asking above, or is it just wishful thinking?

Thanks

[ARGK]

Hi Cieran
I am sure it is possible. The challenge is working out the formula to do it .
I am currently looking at it and will come back to you if I find the solution.
Tony

[kvsrinivasamurthy]

Pl see the attached file with the formula in C4 and dragged down,

Please Login or Register  to view this content.

[Ciaran1986]

Thanks kvsrinivasamurthy, thats a long formula, don't think I could have figured out that one myself! I don't suppose you know if its possible to condition the formula to allow for weekends, or is that asking a bit too much?!

[ARGK]

Hi Ciaran
Please find herewith my solution which takes into account weekends.
Hope it helps.
Good luck.
Tony

[kvsrinivasamurthy]

Pl see the attached file.

[Ciaran1986]

That's great kvsrinivasamurthy, and thanks for your solution aswell Tony. They will allow me set up my work schedule nicely! Thanks again!