我们单位在月末时会从考勤机中导出打卡的数据,EXCEL格式的,但是数据是这样的(下午09:49:18) 文字和时间在同一个单元格内,想把这个数值变为24小时制的,可以吗?请高手指教!
4个回答
假设原时间在A1,可在B1输入公式:
=IF(ISERR(FIND("下午",A1)),RIGHT(A1,8),RIGHT(A1,8)+"12:00")
先把存放数据的单元格整列(假设为A列)变成文本格式(选中该列,右键-设置单元格格式-数字-文本-确定)。
在B1单元格中输入公式:
=IF(MID(A1,1,2)="下午",LOOKUP(MID(A1,4,2),{"01","02","03","04","05","06","07","08","09","10","11","12"},{"13","14","15","16","17","18","19","20","21","22","23","24"})&RIGHT(A1,6),IF(MID(A1,1,2)="上午",RIGHT(A1,8)))
楼上的有个方法可以
不过他的公式有点错误 正确的是
先把存放数据的单元格整列(假设为A列)变成文本格式(选中该列,右键-设置单元格格式-数字-文本-确定)。
在B1单元格中输入公式:
=IF(MID(A1,1,2)="下午",LOOKUP(MID(A1,3,2),{"01","02","03","04","05","06","07","08","09","10","11","12"},{"13","14","15","16","17","18","19","20","21","22","23","24"})&RIGHT(A1,6),IF(MID(A1,1,2)="上午",RIGHT(A1,8)))