Adding time if a cell falls between a certain time interval

[sbrain]

I know the formula for adding time which is:
+TIME (hours, minutes, seconds).

I need to know the formula/code to add TIME to a cell if the cell passes through a fixed time.

For example:

If cell A1 is 9:00AM and cell A2 is 5:00PM, how do I add 15 minutes to 5:00 to make it 5:15PM without having to manually add it everytime? The 15 minutes would be a break during the working shift.
But if cell A1 is 8:00AM and cell A2 is 10:00AM I would not want to add the 15 minutes.


Any help would be appreciated. Thanks!

 

[Ruddles]

Why does 9-5 gets a break and 8-10 doesn't? Is it because the difference between the times has exceeded a certain amount (like four hours, for example) or is it because the period in question goes through a particular time like midday? <!-- / message --><!-- sig -->

 

[Ruddles]

You don't say what the 'fixed time' is but assuming it's midday, try this in cell C1:-

=IF(AND(A1<TIME([COLOR=blue][B]12[/B][/COLOR],0,0),B1>TIME([B][COLOR=blue]12[/COLOR][/B],0,0)),B1+TIME(0,15,0),B1)

 

[sbrain]

How do I add 15 minutes to the time when the time passes through 9AM? I need the cell to add a certain amount of time when passed through a certain time.

 

[Ruddles]

Sorry, I coded a < in my previous post and the forum software got its knickers in a twist.

Put this in C1:-

=IF(AND(A1<TIME(9,0,0),B1>TIME(9,0,0)),B1+TIME(0,15,0),B1)

 

[Ruddles]

Any joy?

 

[Ruddles]

No?

 

[Ruddles]

 

[Ruddles]

 

[Ruddles]