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.

 

[Fluff]

Unfortunately you cannot do that using formulae, unless you enable iterative calculation, which can cause problems.

 

[Olisthoughts]

Can you think of a way to achieve that result?

 

[Olisthoughts]

That would work*

 

[Fluff]

You would need to set iterative calculation for each user & that could adversely affect any other workbooks they use.
Also I'm not sure if you can do that when using Excel online.

 

[Olisthoughts]

What about this clearly wrong formula, does this give you any idea that with your knowledge you could make it work? =IF(ISTEXT"Lunch",TIME(now))

 

[Olisthoughts]

Or what about this one? =IF(ISNUMBER(SEARCH("Lunch",A1)),time,"")

 

[Fluff]

If you use the NOW() function, it will update every time any cell is changed, therefore will not work.

 

[Olisthoughts]

I think I figured it out.

Instead of using just 1 column, I can have either 2 visible columns, or 1 helper hidden column, and another one.

One for current time in case the text matches: =IF(ISNUMBER(SEARCH("Lunch",A1)),NOW(),"")

And one to calculate the return time based on the current time: =IF(ISBLANK(B1),"",B1+TIME(0,30,0))

Can you please help me make this formula work with longer/multiple values using OR functions or something similar?

So it could include all breaks.

 

[Olisthoughts]

I think there's a mistake there, but I'm gonna keep searching to see if I could find a way