一个TEXT函数打天下,如何对日期时间进行分段?
👍
为了对Text的语法有一个感性认识。我们来看几个示例。
示例1:常规应用
=TEXT(B2,"正#;负#;零;请输入数字")
示例2:指鹿为马,颠倒黑白
解释:
=TEXT(B6,"数字;数字;数字;马")
不管是正数负数还是0,都显示为“数字”,如果是文本,不管是什么文本,都显示为“马”
=TEXT(B8,"数字;数字;数字;白")
不管是正数负数还是0,都显示为“数字”,如果是文本,不管是什么文本,都显示为“白”
在了解了TEXT函数的基础知识后,来了解一下Text函数在日期时间中的一些常见应用:
解释:
代码 | 作用 |
---|---|
dddd | 英文星期几 |
aaaa | 中文星期几 |
aaa | 中文星期几的简写,如:星期六显示“六” |
[$-zh-cn]aaa | 返回“周几” |
[dbnum1] | 返回中文小写 |
[dbnum2] | 返回中文大写 |
[dbnum3] | 返回全角阿拉伯数字 |
下面我们只使用Text函数来对日期时间进行分段。
1、划分上午还是下午
原数字 (A9单元格) |
显示效果 | 公式 |
9:30 | 上午 | =TEXT(A10,"上午/下午") |
15:00 | 下午 | =TEXT(A11,"上午/下午") |
9:30 | 上午9:30:00 | =TEXT(A12,"上午/下午h:mm:ss") |
15:23 | 下午3:23:00 | =TEXT(A13,"上午/下午h:mm:ss") |
在自定义格式中可以自动划分上午下午,以12:00为界。
也可用Am和Pm来划分。
2、划分白天还是晚上
如果要划分白天还是晚上,就要稍复杂一点
原数字 | 显示效果 | 公式 |
9:30 | 白天 | =TEXT(TEXT(A14,"h"),"[<20]白天;晚上") |
21:00 | 晚上 | =TEXT(TEXT(A15,"h"),"[<20]白天;晚上") |
解释:
先用TEXT(A14,"h")计算出是几点钟,将其作为最外观TEXT函数的第一参数。如果小于20点,则为白天,否则为晚上。
[<20]是判断条件,判断数字是否小于20。
"[<20]白天;晚上"是TEXT函数格式代码的“变体2.2”:
[条件1]满足条件1时显示的格式 ; 不满足条件1时显示的格式
3、判断是上半年还是下半年
原数字 | 显示效果 | 公式 |
2020-2-1 | 上半年 | =TEXT(TEXT(A16,"m"),"[<7]上半年;下半年") |
2020-6-13 | 上半年 | =TEXT(TEXT(A17,"m"),"[<7]上半年;下半年") |
2020-7-25 | 下半年 | =TEXT(TEXT(A18,"m"),"[<7]上半年;下半年") |
2020-12-31 | 下半年 | =TEXT(TEXT(A19,"m"),"[<7]上半年;下半年") |
解释:
TEXT(A17,"m"),返回日期的月份数。
4、划分上旬、中旬、下旬
原数字 | 显示效果 | 公式 |
2020-2-1 | 上旬 | =TEXT(TEXT(A20,"d"),"[<10]上旬;[<20]中旬;下旬") |
2020-6-13 | 中旬 | =TEXT(TEXT(A21,"d"),"[<10]上旬;[<20]中旬;下旬") |
2020-7-20 | 下旬 | =TEXT(TEXT(A22,"d"),"[<10]上旬;[<20]中旬;下旬") |
2020-12-31 | 下旬 | =TEXT(TEXT(A23,"d"),"[<10]上旬;[<20]中旬;下旬") |
[条件1]满足条件1时显示的格式 ; [条件2]满足条件2时显示的格式 ; 不满足条件1和条件2时显示的格式
5、划分季度
原数字 | 显示效果 | 公式 |
2020-2-1 | 一季度 | =TEXT(TEXT(TEXT(A24,"m"),"[<4]一季度;[<7]二季度;0"),"[<10]三季度;四季度") |
2020-6-13 | 二季度 | =TEXT(TEXT(TEXT(A25,"m"),"[<4]一季度;[<7]二季度;0"),"[<10]三季度;四季度") |
2020-7-20 | 三季度 | =TEXT(TEXT(TEXT(A26,"m"),"[<4]一季度;[<7]二季度;0"),"[<10]三季度;四季度") |
2020-12-31 | 四季度 | =TEXT(TEXT(TEXT(A27,"m"),"[<4]一季度;[<7]二季度;0"),"[<10]三季度;四季度") |
解释:
TEXT(A24,"m")返回日期的月份数。
TEXT函数最多只能判断三种情况,而要判断季度有四种情况,用一个TEXT是没法完成的,所以这里嵌套两层来实现四个种情况的判断。
"[<4]一季度;[<7]二季度;0"意思是月份小于4是返回一季度,月份小于7时返回二季度,前二个情况都不满足,则返回原月份数,留给外层的TEXT函数去处理。
最外层TEXT函数接到里层处理不了的月份数,继续按指定的规则"[<10]三季度;四季度"进行判断:如果小于10,则返回三季度,否则就返回四季度。
自定义格式
TEXT函数
欢迎加入Excel偷懒的技术读者微信群,学习更多Excel技巧。
加偷懒小助手拉你入群,微信号toulanxzs
----------------------
内容:
《偷懒1》内容较广,理念心法、操作技巧、函数公式、实战案例、分析图表、表格美化,都有涉及。可以总结为:传理念、说技巧、学函数、讲图表、美表格《偷懒1》内容较基础,适合入门。
荣誉:
当当网2017年度Excel畅销榜第一名
办公类畅销榜第二名
华章出版社连续三年畅销图书
《偷懒2》相比《偷懒1》难度有所提升,更适合有一定基础的朋友。建议基础较薄弱的表亲,先学习《偷懒1》。
荣誉:
上市当月荣获当当网办公类Excel新书榜第一名。