Add hour in time with condition

[Vishaal]

Excel 2019

In cell A1 we have timestamp
23/06/2018 12:02:18

No in cell B2 we want to add four hours in

but if timestamp is 23/06/2018 14:00:00 and office open and close time is 11:00 am to 5:00 pm then it will add four hours, according to office working hours 24/06/2018 13:00:00 and if Sunday then 25/06/2018 13:00:00

 

[awoohaw]

I don't really understand your IF conditions.
But, to add 4 hours to any time in cell A1, you can do this:

=A1+TIme(4,0,0)

or

= A1 + 4/24

just build your IF conditions around either of those.

 

[Vishaal]

thanks awoohaw,

we just want to say that how to add four hour if today is Saturday and Timestamp is 23/06/2018 14:00:00 and office will close at 15:00 and now office will open on 25/06/2018 11:00:00

 

[awoohaw]

hope this works for you:
This only adds one workday regardless of day of week, it only assumes Sundays are closed and all closing times are 15:00, and all starting times are 11:00.
And the number of hours in the time to add will not go to a 2nd day (in the case the hours cannot be longer than 15:00 - 11:00 = 4 hours.

Book1
	A	B	C	D	E
2				Time to Add	Date/Time of End of time
3	Sat 2018-06-23 14:00:00	close at 15:00	end of 4 hours of work time	04:00:00	Mon 2018-06-25 14:00:00
4	Mon 2018-06-25 14:00:00	open at 11:00
5
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(A3-INT(A3)+D3<=TIME(15,0,0),A3+D3, WORKDAY.INTL(A3,1,"0000001")+TIME(11,0,0)+A3-INT(A3)+D3-TIME(15,0,0))

 

[Vishaal]

Thanks awoohaw, now its the complete solution for us.​

 

[Vishaal]

Can we make this formula short

 

[Vishaal]

Thanks awoohaw,

Can we make the above formula short

 

[awoohaw]

Well, as I have constructed it, that is probably the shortest you can get. Other folks may have other ways to accomplish this.
You could probably swap out the "0000001"

 

[awoohaw]

Sorry for the multiple consecutive post. But it has occurred to me, many hours later that the time segment can be changed to a daily fraction.
This method is not easily understood if anyone is altering / adjusting the formula later. This also has the enumerator (I think that is what it is called) for the "Sunday Only" days off (the number 11, compared to the text string "0000001" is the prior formula:

=IF(A4-INT(A4)+D4<=15/24,A4+D4,
WORKDAY.INTL(A4,1,11)+11/24+A4-INT(A4)+D4-15/24)

 

[Peter_SSs]

Sorry for the multiple consecutive post.

Thanks for considering that.
However, since the two post are only 4 minutes apart, why not edit the first post to add the extra information, as I suggested in my message to you previously?