小伙伴们是不是经常遇到和日期有关的各种问题,比如提取年、月、日,计算月初月末的日期,计算往后推一个月后的日期,多少个工作日后的日期,二个日期的间隔天数,等等。这篇笔记会把这些问题逐一详述。
代码助手下载地址 http://excel880.com/blog/archives/11297
只要在代码区输入 日期 + 空格 ,代码助手就会自动弹出各种相关代码的选项,实现快速输入。
在看到好用的代码时,随时收录,方便下次快捷使用,记得经常做代码库的备份哦。
首先来看下,为什么要转换?
原来的日期数据是这样的:
执行排序代码后的结果:
n = sht.Range("a" & Rows.Count).End(xlUp).Row 'A列最大行
sht.Range("a1").Resize(n).Sort Header:=xlYes, key1:=sht.Range("a1"), order1:=xlAscending '排序
排序后的结果日期顺序仍然是混乱的,因为这些是文本值,无法进行正确的排序。
解决方案:
1. 如果是在excel中,可以这样排序:
2. 使用代码排序,需要先进行转换:
放二组不同类型的数据,一组是A列,仅是日期, 第二组是C列,日期带时间的格式。转换后的结果放在B列和D列
转换代码:
使用了四个函数:
IsDate : 检查是否可以转换成日期,如果是返回 True, 否返回 False
CDate:将字符串转换成日期数据类型,如果不需要输出,使用CDate转换就可以
CLng:将数值转换为 Long 类型
CLng(CDate(x)): 先将日期用 CDate 转化为日期类型,再用 CLng 函数转换,转换后的数值所在的区间段系统会识别为日期
CDbl:将表达式转换为 Double 类型, 与 CLng 同样的原理
转换后不设置格式是这样的结果:
设置成日期格式后就是需要的结果:
这时再执行排序代码后:
二组日期的排序结果全部正确。
注释:
1. 辅助列只是为了演示方便,在实际转换时可以替换原值
2. 不管是否带时间,都可以先使用 CDate 转化为日期类型,再使用 CDbl 转换输出,最后设置成日期
格式。
Excel 函数:
年:=YEAR(A2)
月:=MONTH(A2)
日 :=DAY(A2)
季度:=ROUNDUP(C2/3,0) '月份值/3, 再取整
星期:=WEEKDAY(A2)
代码:
使用2个函数:
DatePart: 返回一个包含已知日期的指定时间部分的值
DatePart(interval,date[,firstdayofweek[,firstweekofyear]])
例如:
datepart("yyyy","2020-1-20") 返回年份: 2020
datePart("q","2020-9-5") 返回季度:3
datepart("m","2020-1-20") 返回月份: 1
datepart("y","2020-3-6") 返回按年计算第几天:66
datePart("d","2020-9-25") 返回天数:25
datepart("w","2020-5-28",vbmonday) 返回星期几: 4
datepart("ww","2020-5-2") 返回第几周: 18
Weekday:返回一个整数,代表某个日期是星期几
Weekday(date, [firstdayofweek])
注释:
本例设置了单元格格式,中文星期,所以 Weekday 要设置为 vbSunday, 中文显示 星期一,数值是 2
datepart ,输出星期时参数也同上, 根据需要是显示数字, 还是中文, 来选择参数
Excel 函数:
本月初:=EOMONTH(A2,-1)+1
本月末:=EOMONTH(A2,0)
下月初:=EOMONTH(A2,0)+1
下月末:=EOMONTH(A2,1)
代码:
DateSerial 函数:返回包含指定的年、月、日的日期
DateSerial(year, month, day)
Excel 函数:
间隔天数:=DATEDIF(A2,B2,"d")
代码:
DateDiff 函数:返回 值,表示两个指定日期间的时间间隔数目。
语法:参数同 DatePart 函数
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
Excel 函数:
增加1个月:=EDATE(A2,1)
20个工作日后:=WORKDAY(A2,20)
代码:
DateAdd 函数:返回加上一段时间间隔的日期。
语法:DateAdd(interval, number, date)
联系客服