问题描述
我的单元格可以包含以下格式的时间:
I have cells that can either contain time in this format:
1625 (2016年为16，第25周为25)或采用这种格式
1625 (16 for 2016 and 25 for week 25)Or in this format
2016-Q2 (如果年份为Q2，则表示第二季度)
2016-Q2 (Q2 means quarter 2 if the year)
转换时，我希望将季度设为季度的中间一周
When converting I want quarters to be the mid week of the quarter
2016-Q1 = 1608
2016-Q1 = 1608
2016年第二季度= 1620
2016-Q2 = 1620
2016-Q3 = 1633
2016-Q3 = 1633
2016-Q4 = 1646
2016-Q4 = 1646
我不想转换其所在单元格中的时间.我想将其转换为另一张工作表中时间轴公式的YYWW格式.因此，我将帮助单元格与转换后的值一起使用，并引用它们而不是另一张工作表中的值.
I dont want to convert the times in the cell its in. I want to convert it to YYWW format for a formula for a timeline in another sheet. So I use help cells with the converted value and reference those instead of the values in the other sheet.
我使用嵌套的if函数来完成这一过程，因为它导致一英里长的公式，因为时间轴需要很长，时间很可能是2025年-第三季度.
I have done this with nested if functions resulting in mile long formulas because the timeline needs to be very long and the time can very well be 2025-Q3.
a = IF('Gulpilspuls NT'！U4 ="2016-Q1"; 1608; IF('Gulpilspuls NT'！U4 ="2016-Q2"; 1620; IF('Gulpilspuls NT'！U4="2016-Q3"; 1633; IF('Gulpilspuls NT'！U4 ="2016-Q4"; 1646; IF('Gulpilspuls NT'！U4 ="2017-Q1"; 1708; IF('Gulpilspuls NT'！U4 ="2017-Q2"; 1720; IF('Gulpilspuls NT'！U4 ="2017-Q3"; 1733; IF('Gulpilspuls NT'！U4 ="2017-Q4"; 1746; IF('Gulpilspuls NT'！U4 ="2018-Q1"; 1808; IF('Gulpilspuls NT'！U4 ="2018-Q2"; 1820; IF('Gulpilspuls NT'！U4 ="2018-Q3"; 1833; IF('Gulpilspuls NT'！U4 ="2018-Q4"; 1846; IF('Gulpilspuls NT'！U4 ="2019-Q1"; 1908; IF('Gulpilspuls NT'！U4 ="2019-Q2"; 1920; IF('GulpilspulsNT'！U4 ="2019-Q3"; 1933; IF('Gulpilspuls NT'！U4 ="2019-Q4"; 1946; IF('Gulpilspuls NT'！U4 ="2020-Q1"; 2008; IF('Gulpilspuls NT'！U4 ="2020-Q2"; 2020; IF('Gulpilspuls NT'！U4 ="2020-Q3"; 2033; IF('Gulpilspuls NT'！U4 ="2020-Q4"; 2046; IF('Gulpilspuls NT'！U4 ="2021-Q1"; 2108; IF('Gulpilspuls NT'！U4 ="2021-Q2"; 2120; IF('Gulpilspuls NT'！U4 ="2021-Q3"; 2133; IF('Gulpilspuls NT'！U4 ="2021-Q4"; 2146; IF('Gulpilspuls NT'！U4 ="2022-Q1"; 2208; IF('Gulpilspuls NT'！U4 ="2022-Q2"; 2220;IF('Gulpilspuls NT'！U4 ="2022-Q3"; 2233; IF('Gulpilspuls NT'！U4 ="2022-Q4"; 2246; IF('Gulpilspuls NT'！U4 ="2023-Q1"; 2308; IF('Gulpilspuls NT'！U4 ="2023-Q2"; 2320; IF('Gulpilspuls NT'！U4 ="2023-Q3"; 2333; IF('Gulpilspuls NT'！U4 ="2023-Q4";2346; IF('Gulpilspuls NT'！U4 ="2024-Q1"; 2408; IF('Gulpilspuls NT'！U4 ="2024-Q2"; 2420; IF('Gulpilspuls NT'！U4 ="2024-Q3"; 2433; IF('Gulpilspuls NT'！U4 ="2024-Q4"; 2446; IF('Gulpilspuls NT'！U4 ="2025-Q1"; 2508; IF('Gulpilspuls NT'！U4 ="2025-Q2; 2520; IF('Gulpilspuls NT'！U4 =" 2025-Q3; 2533; IF('Gulpilspuls NT'！U4 =" 2025-Q4; 2546; IF('Gulpilspuls NT'！U4 =" 2026-Q1; 2608; IF('Gulpilspuls NT'！U4 =" 2026-Q2; 2620; IF('Gulpilspuls NT'！U4 =" 2026-Q3; 2633; IF('Gulpilspuls NT'！U4 =" 2026-Q4; 2646; IF('Gulpilspuls NT'！U4 =" 2027-Q1; 2708; IF('Gulpilspuls NT'！U4 =" 2027-Q2; 2720; IF('Gulpilspuls NT'！U4 ="2027-Q3; 2733; IF('Gulpilspuls NT'！U4 =" 2027-Q4; 2746; IF('Gulpilspuls NT'！U4 =" 2028-Q1; 2808; IF('Gulpilspuls NT'！U4 ="2028-Q2"; 2820; IF('Gulpilspuls NT'！U4 ="2028-Q3"; 2833; IF('Gulpilspuls NT'！U4 ="2028-Q4"; 2846; IF('Gulpilspuls NT'！U4 ="2029-Q1"; 2908; IF('Gulpilspuls NT'！U4 ="2029-Q2"; 2920; IF('Gulpilspuls NT'！U4 ="2029-Q3"; 2933; IF('Gulpilspuls NT'！U4 ="2029-Q4"; 2946; IF('Gulpilspuls NT'！U4 ="2030-Q1"; 3008; IF('Gulpilspuls NT'！U4 ="2030-Q2"; 3020; IF('Gulpilspuls NT'！U4 ="2030-Q3"; 3033; IF('Gulpilspuls NT'！U4 ="2030-Q4"; 3046; IF('Gulpilspuls NT'！U4 ="2031-Q1"; 3108;IF('Gulpilspuls NT'！U4 ="2031-Q2"; 3120; IF('Gulpilspuls NT'！U4 ="2031-Q3"; 3146; IF('Gulpilspuls NT'！U4 ="2031-Q4"; 3146; IF('Gulpilspuls NT'！U4 ="2032-Q1"; 3208; IF('Gulpilspuls NT'！U4 ="2032-Q2"; 3220; IF('Gulpilspuls NT'！U4 ="2032-Q3";3233; IF('Gulpilspuls NT'！U4 ="2032-Q4"; 3246; IF('Gulpilspuls NT'！U4 ="2033-Q1"; 3308; IF('Gulpilspuls NT'！U4 ="2033-Q2"; 3320; IF('Gulpilspuls NT'！U4 ="2033-Q3"; 3333; IF('Gulpilspuls NT'！U4 ="2033-Q4"; 3346; IF('Gulpilspuls NT'！U4 ="2034-Q1; 3408; IF('Gulpilspuls NT'！U4 =" 2034-Q2; 3420; IF('Gulpilspuls NT'！U4 =" 2034-Q3; 3433; IF('Gulpilspuls NT'！U4 =" 2034-Q4; 3446; IF('Gulpilspuls NT'！U4 =" 2035-Q1; 3508; IF('Gulpilspuls NT'！U4 =" 2035-Q2; 3520; IF('Gulpilspuls NT'！U4 ="2035-Q3; 3533; IF('Gulpilspuls NT'！U4 =" 2035-Q4; 3546; IF('Gulpilspuls NT'！U4 =" 2036-Q1; 3608; IF('Gulpilspuls NT'！U4 ="2036-Q2"; 3620; IF('Gulpilspuls NT'！U4 ="2036-Q3"; 3633; IF('Gulpilspuls NT'！U4 ="2036-Q4"; 3646; IF('Gulpilspuls NT'！U4="2037-Q1"; 3708; IF('Gulpilspuls NT'！U4 ="2037-Q2"; 3720; IF('Gulpilspuls NT'！U4 ="2037-Q3"; 3733; IF('Gulpilspuls NT'！U4 ="2037-Q4"; 3746; IF('Gulpilspuls NT'！U4 ="2038-Q1"; 3808; IF('Gulpilspuls NT'！U4 ="2038-Q2"; 3820; IF('Gulpilspuls NT'！U4 ="2038-Q3"; 3833; IF('Gulpilspuls NT'！U4 ="2038-Q4"; 3846;'Gulpilspuls NT'！U4)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
您可以清楚地看到这种方法不是最佳方法.我不能这么长时间，因为如果函数只能包含64个嵌套级别，则嵌套.你们对此有更好的建议吗?
As you can clearly see this method is not the best. I cant make it this long because nested if functions can only contain 64 levels of nesting. Do you guys have a better suggestion for this?
该公式必须同时适用于两种时间输入格式，我需要它能够将所有单元格按1:1转换，这意味着日历中的1个单元格必须是我可以用于其他位置的1个转换后的单元格公式.如果日历中的单元格没有YYYY-Q1234?它应该只是显示它的含义，就像您在公式末尾看到的那样.
The formula has to work with both formats of time entry and I need it to be able to convert all the cells 1:1 meaning 1 cell in the calendar has to be 1 converted cell in the other spot that I can use for the formula. If the cell in the calendar does not have YYYY-Q1234? it should just show what it is instead as you can see at the end of my formula.
推荐答案
编辑:我刚刚意识到，如果原始格式为YYWW，则您不>希望将其更改为季度中周的数字.因此，我们简化了公式:
EDIT: I just realized that if the original is in the format of YYWW, that you do NOT want it changed to the mid quarter week number. So we simplify the formulas:
=IF(ISNUMBER(-A1),A1,MID(A1,3,2) & CHOOSE(RIGHT(A1,1),"08",20,33,46))
，如果您希望YYWW始终显示为数字:
and if you want YYWW to always be rendered as numeric:
=1*IF(ISNUMBER(-A1),A1,MID(A1,3,2) & CHOOSE(RIGHT(A1,1),"08",20,33,46))
这是各种样本的结果:
编辑:如果您需要检查空白，则只需执行以下操作即可:
If you need to check for blanks, you can do this simply:
=IF(LEN(A1)=0,"",1*IF(ISNUMBER(-A1),A1,MID(A1,3,2) & CHOOSE(RIGHT(A1,1),"08",20,33,46)))
但是，如果 0 不会导致下游问题，则可以使用较短的原始公式，而仅使用自定义格式来抑制零返回: 0 ;;
However, if a 0 will not result in a downstream problem, you can use the original, shorter formula, and merely use a custom format to suppress zero returns: 0;;
如果您需要检查其他不想处理的条件，则可以执行类似的操作.
And if you need to check for other conditions for which you don't want to process, you can perform similar actions.
这篇关于如何将四分之一年转换为其他格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！