Calculating a time before midnight and after midnight
Answers
-
Many thanks Paul!!! You saved me again....
Big big thank you and regards all the way from Berlin-Germany. 😉
-
Happy to help all the way from West Virginia-USA. 👍️
-
Hi there,
I am trying to load up another country to my very long formula. However it seems that I am limited.
I am trying to add this: IF(Site@row = "DBI03", "Dubai")
Into this:
=IF(Site@row = "DGM", "Belgium", IF(Site@row = "AAR02", "Denmark", IF(Site@row = "AMS5", "Netherlands", IF(Site@row = "ATH01", "Greece", IF(Site@row = "BEL01", "United Kingdom 2", IF(Site@row = "BLN2", "Germany", IF(Site@row = "BONN01", "Germany", IF(Site@row = "BRC02", "Spain", IF(Site@row = "BRN", "Switzerland", IF(Site@row = "CAE01", "Israel", IF(Site@row = "CAE02", "Israel", IF(Site@row = "CHAN02", "United Kingdom SG", IF(Site@row = "CHAN03", "United Kingdom SG", IF(Site@row = "CGN02", "Germany", IF(Site@row = "CGN03", "Germany", IF(Site@row = "CPH06", "Denmark", IF(Site@row = "CSV", "France", IF(Site@row = "CWY01", "United Kingdom 1", IF(Site@row = "BDLK09", "United Kingdom SG", IF(Site@row = "BDLK10", "United Kingdom SG", IF(Site@row = "BDLK11", "United Kingdom SG", IF(Site@row = "DBLIR3", "Ireland", IF(Site@row = "DLF01", "Germany", IF(Site@row = "EDN04", "United Kingdom 2", IF(Site@row = "ELS01", "Switzerland", IF(Site@row = "ESP02", "Finland", IF(Site@row = "FKF3", "Germany", IF(Site@row = "GWY03", "Ireland", IF(Site@row = "GLVL01", "France", IF(Site@row = "GOT", "Sweden", IF(Site@row = "GPK300", "United Kingdom SG", IF(Site@row = "GSG04", "United Kingdom 2", IF(Site@row = "HBG", "Germany", IF(Site@row = "HPD01", "United Kingdom 2", IF(Site@row = "HPD02", "United Kingdom 2", IF(Site@row = "ILM", "France", IF(Site@row = "ILM05", "France", IF(Site@row = "KAS01", "Germany", IF(Site@row = "KJK04", "Belgium", IF(Site@row = "LIO2", "France", IF(Site@row = "LLE02", "France", IF(Site@row = "LON06", "United Kingdom 1", IF(Site@row = "LON07", "United Kingdom 1", IF(Site@row = "LON11", "United Kingdom 1", IF(Site@row = "LON16", "United Kingdom 1", IF(Site@row = "LON17", "United Kingdom 1", IF(Site@row = "LON18", "United Kingdom 1", IF(Site@row = "LUX1", "Luxembourg", IF(Site@row = "LYS01", "Norway", IF(Site@row = "MAN03", "United Kingdom 2", IF(Site@row = "MDR1", "Spain", IF(Site@row = "MGI01", "France", IF(Site@row = "MHM03", "Germany", IF(Site@row = "MIL01", "Italy", IF(Site@row = "MUC07", "Germany", IF(Site@row = "NTN01", "Israel", IF(Site@row = "NUE01", "Germany", IF(Site@row = "ORS03", "Portugal", IF(Site@row = "ORS04", "Portugal", IF(Site@row = "ORS05", "Portugal", IF(Site@row = "ORS06", "Portugal", IF(Site@row = "ORS07", "Portugal", IF(Site@row = "PDA02", "Italy", IF(Site@row = "REY01", "Iceland", IF(Site@row = "ROL01", "Switzerland", IF(Site@row = "ROM2", "Italy", IF(Site@row = "SAS01", "Norway", IF(Site@row = "SCHP01", "Netherlands", IF(Site@row = "SFT02", "Sweden", IF(Site@row = "STG02", "Germany", IF(Site@row = "STK03", "Sweden", IF(Site@row = "STK06", "Sweden", IF(Site@row = "SVQ01", "Spain", IF(Site@row = "SXB02", "France", IF(Site@row = "SZG02", "Austria", IF(Site@row = "TLS01", "France", IF(Site@row = "TLV01", "Israel", IF(Site@row = "TRD01", "Norway", IF(Site@row = "TRD02", "Norway", IF(Site@row = "UXB10", "United Kingdom 2", IF(Site@row = "VLC01", "Spain", IF(Site@row = "VMCT04", "Italy", IF(Site@row = "VMCT05", "Italy", IF(Site@row = "VNA A", "Austria", IF(Site@row = "WDF02", "Germany", IF(Site@row = "WLSN01", "Switzerland", IF(Site@row = "BCR2", "Romania", IF(Site@row = "BEG02", "Serbia", IF(Site@row = "BTS05", "Slovakia", IF(Site@row = "BUD01", "Hungary", IF(Site@row = "KRK02", "Poland", IF(Site@row = "KRK04", "Poland", IF(Site@row = "KRK07", "Poland", IF(Site@row = "LJU02", "Slovenia", IF(Site@row = "PRG3", "Czechia", IF(Site@row = "PRG5", "Czechia", IF(Site@row = "SJJ01", "Bosnia", IF(Site@row = "TLL04", "Estonia", IF(Site@row = "WAW02", "Poland", IF(Site@row = "ZGR02", "Croatia", IF(Site@row = "ALA03", "Kazakhstan", IF(Site@row = "ASTN02", "Kazakhstan", IF(Site@row = "IEV02", "Ukraine", IF(Site@row = "LED04", "Russia", IF(Site@row = "MOW01", "Russia", IF(Site@row = "MSC", "Russia", IF(Site@row = "MSQ01", "Belarus", IF(Site@row = "OVB02", "Russia", IF(Site@row = "YER03", "Armenia", IF(Site@row = "NBO03", "Kenya", IF(Site@row = "LOS03", "Nigeria", IF(Site@row = "BRNT-1", "South Africa")))))))))))))))))))))))
Is there any way to go around it so that what I need to add will fit in?
Many thanks in advance for your kind feedback.
Best regards,
Andi
-
I would suggest creating a table with the sites in one column and the countries in another. Then you could use
=INDEX({Country Column From Table}, MATCH(Site@row, {Site Column From Table}, 0))
-
Thanks Paul,
Hope all is well your end.
Not a bad solution but Hhmm I am not sure this really helps me, as I am also not expecting to add any more countries/sites to my existing table.
If you have any other solution working directly from the same table I will be grateful.
Best regards,
Andi
-
The problem is that you have reached the maximum number of characters (including spaces).
The only solution I can think of to remove characters enough to make room for more would be to combine like outputs with OR statements, but even then it may not be enough depending on your end result.
For example, looking at a small portion of your formula I see "Russia" output multiple times.
...........IF(Site@row = "LED04", "Russia", IF(Site@row = "MOW01", "Russia", IF(Site@row = "MSC", "Russia", ..........................
This can be consolidated slightly by using an OR like so:
...........IF(OR(Site@row = "LED04", Site@row = "MOW01", Site@row = "MSC"), "Russia", ..........................
You may be able to build out a temporary table just so you can sort it based on the output and rewrite your formula using OR statements which may or may not free up some of your character count.
-
Many thanks Paul,
The IF(OR..) worked perfect.
You are a star!!
Best regards,
Andi
-
Happy to help. 👍️
-
Hi there,
I am trying to create one formula that will relate to two columns. Is it possible?
Meaning, I need something like:
IF, row ((Created By) = "sjacinto@cisco.com" AND row (Region/Coordinator) = "Jacinto", THEN, "Jacinto Sabugueiro")
Below is the formula I have created which unfortunately did not work for me as probably the first IF overtook the second:
=IF([Created By]@row = "sjacinto@cisco.com", "Jacinto Sabugueiro", IF([Region/Coordinator]@row = "Jacinto", "Jacinto Sabugueiro"))
I will be grateful for your kind assistance.
Best regards,
Andi
-
@Andi Reisner Try this...
=IF(AND([Created By]@row = "sjacinto@cisco.com", [Region/Coordinator]@row = "Jacinto"), "Jacinto Sabugueiro")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!