CODEWITHSUNDEEP
excelexcel-formula
Batteredburrito
Batteredburrito

Reputation: 589

Add Time To A Date Only Inside Preset Hours

I am trying to do something as follows:

Date & Time: 27/09/2019 13:28

Working Day: Monday - Friday 8am - 5:30PM (08:00 -> 17:30) (NetworkDays)

Duration: 9.5hrs (09:30 hrs)

Outcome: 30/09/2019 13:28

Logic behind this is

Date & Time + Duration (Only inside the "Working Day") = Outcome

Working Days should only be from Monday - Friday 8am - 5:30pm and excludes weekends. I believe NetworkDays handles this?

If the Date & Time + Duration carries across weekend, then the time carries across to the following Monday.

Is there anything that can handle something like this? Many thanks in advance

Upvotes: 0

Views: 41

Answers (1)

Michael
Michael

Reputation: 4883

If A1 contains the Date/Time and A2 contains the Duration, then:

=WORKDAY(A1,INT((A1-INT(A1)-8/24+A2/24)/9.5*24))+8/24+MOD(A1-INT(A1)-8/24+A2/24,9.5/24)

Upvotes: 2

Related Questions