Adding hours to date/time with limits

**jwillis07** · 11-16-2016, 07:10 AM

Hi all,

Does anyone know how I would go about the following...

If in cell A1, I have a date/time value of: 16/11/16 10:00
If in cell B1, I have an hours value of: 08:00

I can add the hours to the date/time by doing A1+B1 which will return the value of 16/11/16 18:00

What if I wanted to set a time limit so I could only add hours up to 17:00 of any given day, and then recommence at 08:00 the following day?

i.e... in the example above, I'm trying to add 8 hours but there is only 7 available up to 17:00 so the remaining 1 would be added to the following day resulting in a value of 16/11/16 09:00

I'm sure it's possible but not sure how to go about it.

Many thanks,

Jason

**PFDave** · 11-16-2016, 08:09 AM

Do able but you need to outline your logic a bit more clearly please and specify the any given day statement.

**jwillis07** · 11-16-2016, 11:11 AM

Hi PFDave, thanks for the reply.

Basically, it's for me to create a schedule of activities for planning work. So I would only want to use the hours of 08:00-12:00 & 13:00-17:00 of each workday (Mon-Fri).

Some examples...

Start: 16/11/16 08:00 Duration: 06:00 End: 17/11/16 15:00
The duration is 6 hours which when added to the start would equal 14:00 as the end date, but I don't want to include the hour between 12:00 & 13:00, leaving only 8 hours with the remainder being completed after 13:00.

Start: 16/11/16 08:00 Duration: 09:00 End: 17/11/16 09:00
There are 9 hours between 08:00 & 17:00 which is equal to the duration, but I don't want to include the hour between 12:00 & 13:00, leaving only 8 hours with the remainder being completed the first hour of the next workday.

Start: 18/11/16 14:00 Duration: 05:00 End: 20/11/16 10:00
The start date is a friday at 14:00 with a 5 hour duration. 3 hours remaining of the day, with the final 2 hours completed on the next workday (Monday).

As I've written that I realise it's more complicated than I first thought and am starting to see all kinds of trim, workday and if formulas.

Hopefully I've not just made it more unclear!

**jwillis07** · 11-16-2016, 11:12 AM

Hi PFDave, thanks for the reply.

Basically, it's for me to create a schedule of activities for planning work. So I would only want to use the hours of 08:00-12:00 & 13:00-17:00 of each workday (Mon-Fri).

Some examples...

Start: 16/11/16 08:00 Duration: 06:00 End: 17/11/16 15:00
The duration is 6 hours which when added to the start would equal 14:00 as the end date, but I don't want to include the hour between 12:00 & 13:00, leaving only 8 hours with the remainder being completed after 13:00.

Start: 16/11/16 08:00 Duration: 09:00 End: 17/11/16 09:00
There are 9 hours between 08:00 & 17:00 which is equal to the duration, but I don't want to include the hour between 12:00 & 13:00, leaving only 8 hours with the remainder being completed the first hour of the next workday.

Start: 18/11/16 14:00 Duration: 05:00 End: 20/11/16 10:00
The start date is a friday at 14:00 with a 5 hour duration. 3 hours remaining of the day, with the final 2 hours completed on the next workday (Monday).

As I've written that I realise it's more complicated than I first thought and am starting to see all kinds of trim, workday and if formulas.

Hopefully I've not just made it more unclear!

**PFDave** · 11-17-2016, 06:44 AM

What's the most hours for duration? could this involved multiple weekends?

**jwillis07** · 11-17-2016, 07:10 AM

As I'll be recording tasks/activities at a very low level, I would think the longest duration would be no more than 5 days so no it wouldn't cross multiple weekends.

Thank you!

**PFDave** · 11-17-2016, 07:32 AM

OK bear with me and I'll have think, I've done something similar recently, however working out the duration of working hours for multiples sets as opposed to forecasting the end date or due date so I'll see what I can make out.