SOLVED

IFS Formula and time

Copper Contributor

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 (Copper Contributor)
Solution

@stephanieporter 

 

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.

Ah thank you @mathetes !!

 

This is perfect. 

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.

Ive attached some screenshots

@mathetes FIXED IT! 

@stephanieporter 

 

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.

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 

@stephanieporter 

 

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

mathetes_0-1593654467118.png

 

@mathetes Thats worked perfectly! Thank you!

1 best response

Accepted Solutions
best response confirmed by stephanieporter (Copper Contributor)
Solution

@stephanieporter 

 

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.

View solution in original post