Calculating a time before midnight and after midnight

Andi Reisner

Many thanks Paul!!! You saved me again....

Big big thank you and regards all the way from Berlin-Germany. 😉

Paul Newcome

Happy to help all the way from West Virginia-USA. 👍️

Andi Reisner

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

Paul Newcome

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))

Andi Reisner

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

Paul Newcome

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.

Andi Reisner

Many thanks Paul,

The IF(OR..) worked perfect.

You are a star!!

Best regards,

Andi

Paul Newcome

Happy to help. 👍️

Andi Reisner

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

Paul Newcome

@Andi Reisner Try this...

=IF(AND([Created By]@row = "sjacinto@cisco.com", [Region/Coordinator]@row = "Jacinto"), "Jacinto Sabugueiro")