# Tea breaks

#### Star Destroyer

##### New Member
Hi all,

I am fairly new to all of this, so apologies if this is a little simplistic, but I have a spreadsheet in which I input a start time for a teabreak and I want the cell adjacent to automatically show this time + 20 minutes. I have tried using IF but, because the teabreaks run over a long spell in 5 minute increments, the limitations of 7 variables are an obstacle.

Again, I realise this is probably very basic, but we all have to start somewhere and I would genuinely love to get to grips with this software. I know VLOOKUP might factor in there somewhere but I don't know how to apply it to this scenario.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to the Board.

Have you tried eg?

=A1+TIME(0,20,0)

or?

=A1+"00:20"

#### barry houdini

##### MrExcel MVP
Hello Star Destroyer, welcome to MrExcel

If you have a time in A1 then you can get that time + 20 minutes with this formula in B1

=A1+"0:20"

format B1 as a time

#### VoG

##### Legend
With a start time in A1 try

=A1+(20/1440)

and format the result cell as Time. 1440 is the number of minutes in a day.

#### Star Destroyer

##### New Member
Hi all,

Thanks for the responses. My problem is that I was using 9.30 to represent 9:30 and this is because I need the times entered to populate a row with 'T's to show when someone is working and 'B' when someone is on break. This was easy enough to do by using a formula like IF(AND(B3>9.29,B3<9.45),"B",IF(A3="","",IF(A3<9.30,"T",""))))
where A3 is the cell to populate work start time and B3 the cell to populate tea start time.

Since the formula worked with actual numbers the 'greater than' or 'less than' allowed me to bypass multiple IF variables to an extent. Is there any way that I can use IF(AND with times? Merely substituting the numbered decimals with actual times does not work.

Cheers again

#### Andrew Poulsom

##### MrExcel MVP
For serial times you can use:

=IF(AND(B3>"9:29"+0,B3<"9:45"+0),"B",IF(A3="","",IF(A3<"9:30"+0,"T","")))

or:

=IF(AND(B3>TIMEVALUE("9:29"),B3<TIMEVALUE("9:45")),"B",IF(A3="","",IF(A3<TIMEVALUE("9:30"),"T","")))

#### Star Destroyer

##### New Member
Thanks a million for this folks. I love this software and have barely scratched the surface.

Top blokes,

Cheers,

Mark

Replies
0
Views
205
Replies
2
Views
425
Replies
3
Views
2K
Replies
1
Views
367
Replies
17
Views
779

1,195,643
Messages
6,010,889
Members
441,571
Latest member
stolenweasel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back