au9999第一黄金网(第一黄金网即时报价)

Bitget下载

注册下载Bitget下载，邀请好友，即有机会赢取 3,000 USDT

APP下载   官网注册

时间序列在金融、财务等数据分析中占有较大比重，时间类函数是一项非常重要的函数，但在实操过程中我们大多数办公需求中并没有真正地使用好，尤其是财务趋势分析最重要的就是环比同比分析，而这两种分析的方法都离不开时间参数，所以，希望大家也要学好时间函数

本例任务包如下：

问题1：根据身份证号提取出生年月日，并改为日期格式

问题2：根据出生日期计算出当前日期下的年龄

问题3：根据出生日期计算每个人来到世界多少天

问题4：根据出生日期计算每个人出生当日是星期几

问题5：根据出生日期计算每个人出生的前一个月的最后一天的日期

问题6：提取加班时长，分别按取整到小时数，取整到小时数和分钟数

问题7：提取加班时长，取整到小时数和分钟数，小于30分钟的不计算，大于等于30分钟小于60钟的按0.5小时算

基础讲解部分：

Excel时间最多只能处理到秒级别，再往下毫秒则无法处理

时间格式的年份范围是从1900年1月1日至9999年12月31日，对应的数值分别为1和2958465

如下：我们把1900/1/1 的时间格式转为常规显示，可能看到是1，同理9999/12/31则是2958465

我们也可以反向转换，在单元格输入数值1，然后把格式调整为日期格式，我们就会得到1900/1/1

所以在Excel里面1和1900/1/1是等价的，只是两种不同的显示方式，

从这个可以看到一个数值代表时间里的一天，超过2958465这个数值则无法显示为时间格式

时间返回函数：里面都是一个参数（时间）

year 返回日期中的年

month返回日期中的月

day 返回日期中的天

hour 返回日期中的小时

minute 返回日期中的分钟

second返回日期中的秒

以B2单元格作为参数，分别输入以上公式如下：

now() 返回当前的时间戳（动态变化）,参数为空

today() 返回当前的时间日期（到天，动态变化）参数为空

时间可通过自定义更改显示格式

WEEKDAY(serial_number,[return_type]) 返回返回对应于某个日期的一周中的第几天，

默认天数是 1表示（星期日）到 7表示（星期六）范围内的整数，如果想让星期一变为1，则需要把第二个参数填2,如下：

DATE(year,month,day)，此函数返回表示特定日期的连续序列号，一共三个参数，都是必填

第一个参数：年取值范围（0-9999），小于0或者大于9999会报错，我们最好是直接输入4位年份，比如想输入2023年，如果只填23的话，会默认为是1923年，而不会默认是2023年，也就是说填的数小于1900时，会默认用1900进行相加，比如第一个参数输入1899，则函数返回值会变成1899+1900=3799年

负数报错

第二个参数：月，取值为正整数或者负整数，如果参数小于1，则month会从指定年份的上一年12月减去该月份数,

如果参数大于12，则 month 会从指定年份的上一年12月开始加上该月份数

例=DATE(2023,-1,1) 表示从2022年12月份-1个月等于2022年11月1日

=DATE(2023,0,1)表示从2022年12月份-0个月等于2022年12月1日

第三个参数：取整数,如果day大于指定月中的天数，则day会从该月的第一天开始加上该天数,如果 day 小于 1，则day从指定月份的第一天开始减去该天数，然后再加上1天

例=DATE(2023,1,38)表示从2023年1月1日（包括当天）开始加38天，38-31=7天，所以这个日期等于2023年2月7日

与此类似的函数还有一个TIME(hour, minute, second)

里面三个参数，对应时分秒，由 TIME 返回的十进制数字是一个范围在 0（零）到 0.99988426 之间的值，表示 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 之间的时间

DATEVALUE(date_text）

DATEVALUE 函数将存储为文本的日期转换为 Excel 识别为日期的序列号，里面只有一个参数，也就是文本格式的日期，例:把文本格式的单元格转化为日期格式，然后可以把44954变成日期显示

EOMONTH(start_date, months)

返回某个月份最后一天的时间序列号,EO是英文end of的首字母缩写，end of month也就是月底的意思,使用函数 EOMONTH 可以计算正好在特定月份中最后一天到期的到期日

第一个参数是起始日期，需要填日期格式的参数，第二个参数是月份数，如果填0则代表取当月的月末最后一天的日期，注意如果直接输入日期参数，则要用DATE函数进行参数输入

举例：求2023年2月5日所在月份的最后一天的日期

=EOMONTH(DATE(2023,2,5),0) 如果第第二参数是1代表日期取3月份的最后一天

如果日期在单元格里作为参数，则直接引用就行

但是如果写成这样EOMONTH(2022/2/5,0)，就出错了，

这里第一个参数并不代表2022年2月5日这个日期，而是代表除法计算 = 2022/2/5=202 数字代表的日期1900年7月20日，是计算这个月份的最后一天了

DAYS(end_date, start_date)，这个函数计算两个日期之间相差的天数

第一个参数是起始日期，第二个参数是结束日期

例：计算假如有个人生日是2000年1月5日，计算截止到目前他来到这个世界多少天？

=-DAYS(DATE(2000,1,5),NOW()) 第一个参数依然用DATE函数进行输入，第二个用了动态函数NOW,提取目前的时间节点，然后第一个参数与第二个参数相减，后面的数比前面的大，所以前面加负数进行取正数值

重要函数

DATEDIF:这个是日期类函数最重要的一个，但在Excel中却是隐藏函数，所谓隐藏函数就是你在插入函数界面和帮助界面都查不到这个函数相关信息，但是直接输入这个公式却可以正常使用

虽然Excel找不以，但是WPS却收录囊中：

DATEDIF函数，date是日期，dif是单词difference的缩写，函如其名就是主要用于计算两个日期之间的天数、月数或年数。其返回的值是两个日期之间的年\月\日间隔数。 应用场景包括计算年龄，工龄，账龄，员工考勤，日期倒计时等等

DATEDIF（Start_Date,End_Date,Unit）

第一个参数是超始日期，第二个参数是结束日期，

第三个参数比较重要，是返回值类型

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 起始日期与结束日期的同月间隔天数，忽略日期中的月份和年份。

"YD" 起始日期与结束日期的同年间隔天数，忽略日期中的年份。

"YM" 起始日期与结束日期的同年间隔月数，忽略日期中的年份。

其中第三个参数为’D’时相当于DAYS函数

示例：令某人出生于1991年10月8日，现在日期为2023年1月28日

计算年龄:=DATEDIF(B2,C2,"Y")

可以看到这个结果为31，是直接取两个时间日期之间差值的绝对值

计算来到这个世界多少天：=DATEDIF(B2,C2,"D")

计算来到这个世界多少个月：=DATEDIF(B2,C2,"M")

参数带忽略的用法示例：

令阿强的出生年月为1991年10月8日，令阿珍的出生年月为1998年4月19日

此时计算两人生日之间的相差天数，相差月份：

一、Excel实现

1根据身份证号提取出生年月日，并改为日期格式

=DATE(MID(L2,7,4),MID(L2,11,2),MID(L2,13,2))

2根据出生日期计算出当前日期下的年龄

=DATEDIF(M2,NOW(),"Y")

注意这个年龄和前面的年龄不一样，前面是直接用年这个参数相减得到的年龄，用DATEDIF取的话是考虑到月份和天数，比如1991年1月29日出生的人，当下日期为2023年1月28日，如果用年数相减得到今年32岁，但是用DATEDIF取的话就是31岁，因为精确到天数的话并没有满1年，取不到32，如下：

3根据出生日期计算每个人来到世界多少天

=DATEDIF(M2,NOW(),"D")

4根据出生日期计算每个人出生当日是星期几

=WEEKDAY(M2,2)

我们得到的星期只是数学显示，需要调整成显示”星期几”这种格式，找到自定义格式，选4个a那一项，或者把公式直接用TEXT函数转为4个a格式：=TEXT(WEEKDAY(M2,2),"aaaa")

=TEXT(Value you want to format, "Format code you want to apply")

TEXT本身是文本的意思，该函数可通过格式代码向数字应用格式，进而更改数字的显示方式，说直白些就是把一个数值转换为文本格式

第一个参数是要转换为文本的数值

第二个参数是转换为文本的显示格式

5根据出生日期计算每个人出生的前一个月的最后一天的日期

=EOMONTH(M2,-1)

6提取加班时长，分别按取整到小时数，取整到小时数和分钟数

取整到小时数：输入公式=TEXT(C2-B2,"H")，返加的是两个时间的小时差，不满60钟的都将不计算在内

取整到小时数和分钟数，输入公式：

E2单元格=LEFT(TEXT(C2-B2,"MM/SS"),2)

我们也可以在输入公式=TEXT(C2-B2,"H/MM/SS")，得到是完整的时间差

7提取加班时长，取整到小时数和分钟数，分钟数以30分钟为单位，小于30分钟的不计算，大于等于30分钟小于60钟的按0.5小时算

这个问题小时数好解决，就是=TEXT(C2-B2,"H")这个公式，

这里需要对分钟数进行取舍=INT(LEFT(TEXT(C2-B2,"MM/SS"),2)/30)*0.5

我们可以看到加班分钟数小于30分钟的按0小时计，大于等于30分钟的按0.5小时计，然后把这个公式加到D列单元格公式即可，D列则是我们要的结果

最终取数公式=TEXT(C2-B2,"H")+INT(LEFT(TEXT(C2-B2,"MM/SS"),2)/30)*0.5

我们也可以用文字进行连接

=TEXT(C2-B2,"H小时MM分钟")

1. Python实现：

1根据身份证号提取出生年月日，并改为日期格式

代码如下：

import pandas as pd

import datetime

df = pd.read_excel("c:/study_note/xiao_datedif.xlsx",sheet_name="花名册")

a= ["出生日期","计算年龄","来多少天","出生星期几","出生前一个月的最后一天"]

col_name.extend(a)

col_name=['姓名','性别','年龄','技能','身份证号码',

'出生日期','计算年龄','来多少天','出生星期几','出生前一个月的最后一天','婚姻状况','学历',

'毕业院校','专业','是否最新']

df = df.reindex(columns=col_name)

df["身份证号码"] = df["身份证号码"].astype(str)

df["出生日期"] = df["身份证号码"].apply(lambda x: x[6:14])

df["出生日期"] = pd.to_datetime(df["出生日期"])

2根据出生日期计算出当前日期下的年龄

a =pd.Timestamp.now()

df["计算年龄"] = df["出生日期"].apply(lambda x: int(pd.Timedelta(a-x).days/365))

3根据出生日期计算每个人来到世界多少天

a =pd.Timestamp.now()

df["来多少天"] = df["出生日期"].apply(lambda x: pd.Timedelta(a-x).days)

4根据出生日期计算每个人出生当日是星期几

week_list = ["星期一","星期二","星期三","星期四","星期五","星期六","星期日"]

week_list[df["出生日期"][0].weekday()]

df["出生星期几"]=df["出生日期"].apply(lambda x:week_list[x.weekday()])

5根据出生日期计算每个人出生的前一个月的最后一天的日期

def lastday(year,month):

weekday,monthcountdays = calendar.monthrange(year,month)

lastday = datetime.date(year,month,monthcountdays)

return lastday

for i in range(len(df)):

if df["出生日期"][i].month == 1:

a = df["出生日期"][i].year -1

b = 12

else:

a = df["出生日期"][i].year

b = df["出生日期"][i].month

df["出生前一个月的最后一天"][i] = lastday(a,b)

问题6：提取加班时长，分别按取整到小时数，取整到小时数和分钟数

import pandas as pd

import datetime

df = pd.read_excel("c:/study_note/time.xlsx")

df["开始加班"] = str(datetime.date.today()) +" "+ df["开始加班"].astype(str)

df["结束加班"] = str(datetime.date.today()) +" "+ df["结束加班"].astype(str)

df["开始加班"] = pd.to_datetime(df["开始加班"])

df["结束加班"] = pd.to_datetime(df["结束加班"])

df["加班时长"] = df["结束加班"]-df["开始加班"]

这个得到的就是加班时长明细，如果只取整到小时数，则最下面代码改为：

df["加班时长"] = None

for i in range(len(df)):

df["加班时长"][i] = int((df["结束加班"][i]-df["开始加班"][i]).seconds/3600)

即可得到整数的加班时长

问题7：提取加班时长，取整到小时数和分钟数，小于30分钟的不计算，大于等于30分钟小于60钟的按0.5小时算

import pandas as pd

import datetime

df = pd.read_excel("c:/study_note/time.xlsx")

df["开始加班"] = str(datetime.date.today()) +" "+ df["开始加班"].astype(str)

df["结束加班"] = str(datetime.date.today()) +" "+ df["结束加班"].astype(str)

df["开始加班"] = pd.to_datetime(df["开始加班"])

df["结束加班"] = pd.to_datetime(df["结束加班"])

df["加班时长"] = None

for i in range(len(df)):

df["加班时长"][i] = int((df["结束加班"][i]-df["开始加班"][i]).seconds/3600/0.5)*0.5

SQL实现：

问题1：根据身份证号提取出生年月日，并改为日期格式

ALTER TABLE names ADD 出生年月 DATE AFTER 身份证号码;

CREATE TABLE temp5

(姓名 VARCHAR(10),

出生年月 DATE

);

INSERT INTO temp5 SELECT 姓名,mid(身份证号码,7,8) FROM names;

UPDATE names,temp5 SET names.出生年月 = temp5.出生年月 WHERE names.姓名 = temp5.姓名;

问题2：根据出生日期计算出当前日期下的年龄

SELECT 姓名,floor(datediff(now(),出生年月)/365) AS 年龄 FROM names ;

问题3：根据出生日期计算每个人来到世界多少天

SELECT 姓名,datediff(now(),出生年月) AS 来了多少天 FROM names ;

问题4：根据出生日期计算每个人出生当日是星期几

SELECT 姓名,weekday(出生年月)+1 AS 出生星期几 FROM names ;

问题5：根据出生日期计算每个人出生的前一个月的最后一天的日期

CREATE TABLE temp6

(

姓名 VARCHAR(10),

days INT);

INSERT INTO temp6 SELECT 姓名,dayofmonth(出生年月)FROM names;

SELECT DISTINCT names.姓名,date_sub(出生年月,INTERVAL temp6.days DAY) AS 出生前一月最后一天

FROM names LEFT OUTER JOIN temp6 ON names.姓名 = temp6.姓名;

问题6：提取加班时长，分别按取整到小时数，取整到小时数和分钟数

CREATE TABLE overtime

(

姓名 VARCHAR(10),

开始加班 TIME,

结束加班 TIME

);

SELECT 姓名,timediff(结束加班,开始加班) AS 加班时长 FROM overtime ;

SELECT 姓名,timeSTAMPdiff(HOUR,开始加班,结束加班) AS 加班时长 FROM overtime ;

问题7：提取加班时长，取整到小时数和分钟数，小于30分钟的不计算，大于等于30分钟小于60钟的按0.5小时算

SELECT 姓名,floor(timeSTAMPdiff(MINUTE,开始加班,结束加班)/30)*0.5 AS 加班时长 FROM overtime;

Tableau实现：

问题1：根据身份证号提取出生年月日，并改为日期格式

连接到花名册表格，将身份证号码字段转为维度

类型改为字符串

创建计算字段出生年月，输入公式：

MAKEDATE(INT(MID([身份证号码],7,4)),INT(MID([身份证号码],11,2)),INT(MID([身份证号码],13,2)))

然后把出生年月字段拖到文本上面，显示为精确日期即可

问题2：根据出生日期计算出当前日期下的年龄

创建计算字段“计算年龄”，输入公式:DATEDIFF("year",[出生年月],now())

把计算年龄字段拖到文本上面即可

问题3：根据出生日期计算每个人来到世界多少天

创建计算字段“来多少天”，输入公式：DATEDIFF("day",[出生年月],NOW())

把来多少天字段拖到文本，再用鼠标点一下右上角图表类型即可

问题4：根据出生日期计算每个人出生当日是星期几

创建计算字段出生星期几，输入公式：DATEPART("weekday",[出生年月])

把出生星期几字段拖到文本，再用鼠标点一下右上角图表类型即可

问题5：根据出生日期计算每个人出生的前一个月的最后一天的日期

创建计算字段出生前一个月的最后一天，输入公式:DATE(DATEADD("day",-DAY([出生年月]),[出生年月]))

将字段值类型改为日期格式：

将字段移到文本处，筛选精确日期即可

问题6：提取加班时长，分别按取整到小时数，取整到小时数和分钟数

第1步连接到加班表，然后把开始加班和结束加班两个字段全都改为字符串格式，如下：

然后创建一个计算字段，开始加班时间，输入公式：

MAKETIME(int(mid([开始加班],1,2)),int(MID([开始加班],4,2)),int(mid([开始加班],7,2)))

因为Tableau软件无法识别单独的时间格式，我们这里将原表的时间转换为字符串，再通过函数构建为日期时间格式

同理再创建一个结束加班时间字段

MAKETIME(int(mid([结束加班],1,2)),int(MID([结束加班],4,2)),int(mid([结束加班],7,2)))

然后创建一个计算字段命名为“加班小时数”,输入公式：DATEDIFF("hour",[开始加班时间],[结束加班时间])

将加班小时数拖到文本上面，即可得到取整的小时数

创建字段”加班分钟数”:输入公式DATEDIFF("minute",[开始加班时间],[结束加班时间])%60

然后把加班分钟数拖到文本上面，再用鼠标点一下右上角图表类型部分，即可得到加班分钟数

问题7：提取加班时长，取整到小时数和分钟数，小于30分钟的不计算，大于等于30分钟小于60钟的按0.5小时算

创建计算字段：“加班小时数”输入公式：2floor(DATEDIFF("minute",[开始加班时间],[结束加班时间])/30)*0.5

将加班小时数2字段报到文本上面，即可得到按分钟舍入小时数的结果

相关内容

标签： 返回日期中 日期格式 输入公式