SOLVED

IFS Formula and time

%3CLINGO-SUB%20id%3D%22lingo-sub-1500469%22%20slang%3D%22en-US%22%3EIFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500469%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20needing%20help%20with%20a%20IFS%20and%20time%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20%3D%20Time%3C%2FP%3E%3CP%3EColumn%20B%20%3D%20Time%20zone%20(so%20will%20just%20be%20a%20drop%20down%20box%20list%20LON%2C%20NYC%2C%20HK%2C%20ANZ)%3C%2FP%3E%3CP%3EColumn%20C%20%3D%20Time%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20basically%2C%20I%20need%20a%20formula%20that%20says%20all%20of%20the%20following%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20LON%20then%20Column%20C%20%3D%20Column%20A%26nbsp%3B%20%2B%209%20hours%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20NYC%20then%20Column%20C%20%3D%20Column%20A%26nbsp%3B%20%2B%2014%20hours%3C%2FP%3E%3CP%3EIF%20Column%20A%20%3D%20HK%20then%20Column%20C%20%3D%20Column%20A%26nbsp%3B%20%2B%202%20hours%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20A%20and%20C%20are%20formatted%20in%20time.%20How%20do%20I%20write%20this%20formula%20for%20Column%20B%3F%20It%20doesn't%20seem%20to%20be%20working%20with%20the%20cell%20being%20in%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1500469%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500485%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500485%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713991%22%20target%3D%22_blank%22%3E%40stephanieporter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20recommend%20creating%20a%20table%20for%20the%20amounts%20to%20be%20added....as%20I've%20done%20in%20the%20attached.%20The%20first%20column%20also%20serves%20as%20the%20basis%20for%20the%20drop%20down%20list%20in%20selecting%20the%20city.%3C%2FP%3E%3CP%3EThe%20formula%20is%20mostly%20a%20matter%20of%20entering%20the%20numbers%20in%20%22time%22%20format.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500605%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500605%22%20slang%3D%22en-US%22%3E%3CP%3EAh%20thank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20perfect.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500613%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500613%22%20slang%3D%22en-US%22%3EIve%20just%20tried%20it%20in%20my%20spreadsheet%20and%20I'm%20getting%20errors!%20I%20dont%20know%20what%20I'm%20doing%20wrong.%20I%20just%20copied%20your%20formula%20but%20applied%20it%20to%20the%20relevant%20cells.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20actually%20should%20have%20a%20fourth%20option%3CBR%20%2F%3EANZ%200%3A00%3A00%3CBR%20%2F%3ELON%209%3A00%3A00%3CBR%20%2F%3ENYC%2014%3A00%3A00%3CBR%20%2F%3EHK%202%3A00%3A00%3CBR%20%2F%3E%3CBR%20%2F%3EI%20also%20have%20the%20formulas%20on%20another%20tab.%20Is%20that%20why%3F%20I%20selected%20the%20tab%20and%20the%20right%20area%20I%20dont%20know%20what%20Ive%20done%20wrong.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500617%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500617%22%20slang%3D%22en-US%22%3E%3CP%3EIve%20attached%20some%20screenshots%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500621%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BFIXED%20IT!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1501348%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1501348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713991%22%20target%3D%22_blank%22%3E%40stephanieporter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20great.%20It's%20always%20better%20to%20have%20fixed%20it%20yourself.%20Makes%20for%20a%20much%20better%20learning%20experience.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%2C%20don't%20hesitate%20to%20come%20back%20with%20questions%20when%20you%20get%20stumped.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502359%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20Formula%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502359%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20so%20much!%20Do%20you%20have%20any%20idea%20how%20to%20make%20column%20A%20a%20time%20only%20picker%3F%20I%20already%20have%20a%20column%20which%20is%20a%20date%20picker.%20I%20want%20a%20second%20date%20picker%20for%20column%20a%20but%20I%20want%20it%20to%20be%20time%20only.%20I'm%20not%20sure%20how%20to%20code%20for%202%20different%20time%20pickers%20in%20the%20same%20project.%20Doesn't%20seem%20to%20work.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted
Best Response confirmed by stephanieporter (Occasional 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.

Highlighted

Ah thank you @mathetes !!

 

This is perfect. 

Highlighted
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.

Highlighted

Ive attached some screenshots

Highlighted

@mathetes FIXED IT! 

Highlighted

@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.

Highlighted

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 

Highlighted

@stephanieporter 

 

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

mathetes_0-1593654467118.png

 

Highlighted

@mathetes Thats worked perfectly! Thank you!