跳到主要内容

如何在Excel中将时间字符串转换为时间?

例如,您在工作表中有一个文本字符串列表,现在您需要将这些文本字符串转换为时间或日期和时间,如何解决? 在这里,我将向您介绍一些技巧,以快速将文本字符串转换为Excel中的时间。

将文本字符串转换为时间

将文本字符串转换为日期和时间


箭头蓝色右气泡 将文本字符串转换为时间

要将文本字符串转换为时间,可以使用一些公式来求解。

1.选择一个单元格并键入此公式 = TIMEVALUE(LEFT(A1,LEN(A1)-2)&“:”&RIGHT(A1,2)) (A1是您需要转换为时间的文本字符串),然后按 输入。 如果需要,可以将填充手柄拖到所需的范围。 看截图:
文档时间字符串到时间 1

2.然后右键单击这些选定的单元格,然后选择 单元格格式 从上下文菜单中,然后在 单元格格式 对话框中选择 时间 来自 产品分类 在下面列出 联系电话 标签,然后选择所需的时间类型。
文档时间字符串到时间 2

3。 点击 OK,现在文本字符串将转换为时间。
文档时间字符串到时间 3

请注意:

1.当包含秒的文本字符串(例如120158)时,此公式无法正常工作。

2.如果您的文本字符串格式为120456P,则可以使用此公式 =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2),然后将单元格设置为您需要的12小时制。 看截图:

文档时间字符串到时间 4
文档时间字符串到时间 5

箭头蓝色右气泡 将文本字符串转换为日期和时间

如果您的文本字符串包含日期和时间(例如20141212 0312),则可以使用一些长公式来解决。

1.在空白单元格中,键入此公式 =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),RIGHT(A1,2),0),然后按 输入 键,如果需要,可以将此公式应用于范围。

2.然后右键单击选定的单元格,然后选择 单元格格式 从上下文菜单中,然后在 单元格格式 对话框中选择 日期 来自 产品分类 列表,然后从右侧选择所需的类型。
文档时间字符串到时间 6

3。 点击 OK。 现在您可以看到文本字符串转换为日期和时间。
文档时间字符串到时间 7

小费。如果要将时间转换为十进制小时,分钟或秒,请尝试使用 Kutools for Excel转换时间 如以下屏幕截图所示。 它在30天内无限制地提供完整功能, 请下载并立即免费试用。

在Excel中快速将时间转换为十进制值

例如,您有一个需要转换为十进制小时,分钟或秒的时间列表,如何在Excel中快速轻松地解决该问题? 的 转换时间 of Kutools用于 Excel,可以为您帮个忙。   点击免费试用30天!
doc转换时间
 
Kutools for Excel:拥有300多个便捷的Excel加载项,可以在30天内免费试用。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Would love any help with this. I have data that is reported as "1d 01h 01m" representing 1 day, 1 hour, 1 min and I need to be able to represent this as a single number (preferably in # of minutes) any thoughts on how I can convert this string to a number value? Thanks all!
This comment was minimized by the moderator on the site
how to convert 06Hr:11Mi into 06:11
This comment was minimized by the moderator on the site
Hi, abinash kumar, you can remove alpha characters from your time string.
This tutorial can help you:
How to remove alpha characters from cells in Excel?
This comment was minimized by the moderator on the site
how do I convert Date/Time with this text format 08/07/2022 1519
This comment was minimized by the moderator on the site
Hi, Tina, please give me a specific example for explaining and descriping your problem. Thank you.
This comment was minimized by the moderator on the site
Hi there!

I used one of formulas above to convert a time string and it worked beautifully expect for anything that had a format of 1200P. It should convert to 12:00 but instead converts to 00:00. Any thoughts on why or how to fix it?

I used: "120456P, you can use this formula =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2)"

I did not have any seconds in my time string so I deleted the last set of 0's in the formula and it still worked.
This comment was minimized by the moderator on the site
Hi.,I have a query.
I want to convert a duration extracted in a format #h #m #s to duration format so that I can filter it. For example:
My data has a field named Session Duration and the format of the cell is "General" and the cell says "1h 2m 15s". I need to convert this data into 01:02:15 but not in time format but a duration. Is there a way to do that.
This comment was minimized by the moderator on the site
=A1/(24*60) (algebra methods)..hahhaha
then apply time format
This comment was minimized by the moderator on the site
How can I convert 1 Hour 35 Min 25 Sec, stored as a text format into Hour format (hh:nn:ss)?
This comment was minimized by the moderator on the site
Hi, Gabriel, you can try this formula =TIME(LEFT(K4,1),MID(K4,8,2),MID(K4,15,2)) K4 is the cell contains text you want to convert, then format the result as time.
This comment was minimized by the moderator on the site
2h 50m how do i convert into 2:50
This comment was minimized by the moderator on the site
Hi, Ayaan, you can try Find and Replace function, h (blank) replace wth : , m replace with nothing
This comment was minimized by the moderator on the site
using the [ =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2) ] formula,
in example #2 above, 123706A >> gets converted to 12:37:06 PM (instead of 12:37:06 AM)
how may this be rectified?
This comment was minimized by the moderator on the site
You only need to change the P to A in the formula =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="A")/2), the format the formula cell as hh:mm:ss AM/PM
This comment was minimized by the moderator on the site
so if you enter 35943A you get 3:59:43 AM (which is great).
but if you enter 123706A you would expect to get 12:37:06 AM but instead you get 12:37:06 PM (which is not so great).
also if you enter 123706P you get 12:37:06 AM (again expecting to get 12:37:06 PM).
the meridiem are only incorrect for 12xxxx, formula works flawlessly for 1~11.
any way to improve the formula, to be all encompassing? thank you
This comment was minimized by the moderator on the site
It is a little complex, sorry I cannot help you. Maybe someone in our forum https://www.extendoffice.com/forum.html can help you if you place the question in it.
This comment was minimized by the moderator on the site
using the [ =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2) ] formula,
in example #2 above, 123706A >> gets converted to 12:37:06 PM (instead of 12:37:06 AM)
how may this be rectified?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations