If specific text in cell A, then current time + other time in cell B

[Olisthoughts]

Hello,

We have a simple break list at work. I spent a lot of time making a better one, but the manager said it's too complicated.

I want to improve the one we have now at least a little. (screenshot attached)

In this break list, when you click on a cell under the "status" column - you have 4 selections available, like templates. (15 minute break / Lunch / short break) The problem is we have to manually calculate the time that we will return at.

As soon as I enter the text Lunch in status I want the next cell to calculate what time I will be back at.

I want to have a pseudo-formula like this "If cell A = "Lunch", then current time +time 30 minutes, else if cell A = "break 15 min", then current time + time 15 minute, else if cell A = "short break", then nothing/5-7 minutes (short break is flexible), else nothing"

This break list is uploaded in Microsoft Teams, so code/macro will probably not work. A formula is needed.

My only experience with formulas was when building the other break list and some functions to add I can take from what I learned then. But I don't how to make this one work.

Thank you.

 

[Olisthoughts]

Maybe adding an AND function after current time?

 

[Fluff]

Format C1 to show hh:mm:ss then try changing any cell on that sheet & see what happens to the time in C1.

The ONLY way is VBA or to enable iterative calculation and I don't think either of them work with Excel online.

 

[Olisthoughts]

I see.

Can you please write that in VBA?

I will show the manager how much easier it is, and maybe they'll switch to Excel locally.

 

[Fluff]

This needs to go in the relevant sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Select Case Target.Value
         Case "Lunch": Target.Offset(, 1) = Time + 0.5 / 24
         Case "Break 15 min": Target.Offset(, 1) = Time + 0.25 / 24
      End Select
   End If
End Sub

All users will have to enable macros & only one person can use the workbook at any one time.
Personaly I would just get them to enter the time manually. It's a lot simpler & easier.