将用户定义的代码转换为它代表的组件 [英] Converting a User-defined code into the Components it stands for

问题描述

您好，



这是一个非常具体的问题，我希望有人可能知道如何做到这一点。

Hello,

This is a very specific question, and I was hoping someone might know how I could go about doing this.

在我的组织中，我们使用4位数代码进行季度/年度组合。例如，0983代表1998年春季，1111代表2011年冬季。




这些代码的逻辑方式是定义的，所以我想知道是否有是一种创建if / then公式或类似方法的方法，可以将代码分解回它所代表的组件。

In my organization, we use 4-digit codes for quarter/year combos. For example, 0983 stands for Spring 1998 and 1111 stands for Winter 2011.

There's a logical way these codes are defined, so I was wondering if there was a way to create an if/then formula or something similar to break the code back into the components it stands for.

第一个数字是世纪，所以0xxx与Quarter 19xx相关。接下来的两位数是年份，因此x14x代表Quarter xx14。最后一位是该季度所在的月份 - 1月/冬季为1，3月/春季为3，6月/夏季为6，9月/秋季为
为9.因此xxx9为秋季xxxx。



那么，有没有办法我可以创建一个足够灵活的if / then公式来允许这些不同的组件。例如（我知道这不正确）IF（A1 ='0 ###），（B1 ='xxxx 19 ##'）等等。

The first digit is the century, so 0xxx correlates to Quarter 19xx. The next two digits are the year, so x14x stands for Quarter xx14. The last digit is the month the Quarter is located in - January/Winter is 1, March/Spring is 3, June/Summer is 6, and September/Fall is 9. So xxx9 is Fall xxxx.

So, is there a way I could create an if/then formula that was flexible enough to allow for these different components. For example (this will be incorrect I know) IF(A1='0###),(B1='xxxx 19##') etc.

推荐答案

在我的组织中，我们使用4位数代码进行四分之一/年组合。例如，0983代表1998年春季，1111代表2011年冬季。

In my organization, we use 4-digit codes for quarter/year combos. For example, 0983 stands for Spring 1998 and 1111 stands for Winter 2011.

=CHOOSE(MATCH(VALUE(RIGHT(A1)),{1,3,6,9}),"Winter","Spring","Summer","Fall") & " " & 19+LEFT(A1)&MID(A1,2,2)

我假设A1包含4位数的文本代码作为样本。

I assume that A1 contains a 4 digit text code as your samples.

Andreas。

这篇关于将用户定义的代码转换为它代表的组件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！