SOLVED

# IFS Formula and time

Occasional Contributor

# IFS Formula and time

Hi,

I'm needing help with a IFS and time formula.

Column A = Time

Column B = Time zone (so will just be a drop down box list LON, NYC, HK, ANZ)

Column C = Time

So basically, I need a formula that says all of the following

IF Column A = LON then Column C = Column A  + 9 hours

IF Column A = NYC then Column C = Column A  + 14 hours

IF Column A = HK then Column C = Column A  + 2 hours

Both A and C are formatted in time. How do I write this formula for Column B? It doesn't seem to be working with the cell being in time.

Thanks!

9 Replies
best response confirmed by stephanieporter (Occasional Contributor)
Solution

# Re: IFS Formula and time

I'd recommend creating a table for the amounts to be added....as I've done in the attached. The first column also serves as the basis for the drop down list in selecting the city.

The formula is mostly a matter of entering the numbers in "time" format.

# Re: IFS Formula and time

Ah thank you @mathetes !!

This is perfect.

# Re: IFS Formula and time

Ive just tried it in my spreadsheet and I'm getting errors! I dont know what I'm doing wrong. I just copied your formula but applied it to the relevant cells.

I actually should have a fourth option
ANZ 0:00:00
LON 9:00:00
NYC 14:00:00
HK 2:00:00

I also have the formulas on another tab. Is that why? I selected the tab and the right area I dont know what Ive done wrong.

# Re: IFS Formula and time

Ive attached some screenshots

# Re: IFS Formula and time

@mathetes FIXED IT!

# Re: IFS Formula and time

That's great. It's always better to have fixed it yourself. Makes for a much better learning experience.

Nevertheless, don't hesitate to come back with questions when you get stumped.

# Re: IFS Formula and time

Thanks so much! Do you have any idea how to make column A a time only picker? I already have a column which is a date picker. I want a second date picker for column a but I want it to be time only. I'm not sure how to code for 2 different time pickers in the same project. Doesn't seem to work. @mathetes

# Re: IFS Formula and time

Do you know aabout "data validation"? Use that and select "time" as the type of data.

# Re: IFS Formula and time

@mathetes Thats worked perfectly! Thank you!