Tea breaks

[Star Destroyer]

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.

Thanks in advance

 

[Andrew Poulsom]

Welcome to the Board.

Have you tried eg?

=A1+TIME(0,20,0)

or?

=A1+"00:20"

 

[barry houdini]

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]

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]

ADVERTISEMENT

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]

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]

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

Top blokes,

Cheers,

Mark