TimeValue(Substitute)

nance1432m

New Member
Joined
Aug 7, 2019
Messages
2
Hi, I am working with a spreadsheet that was pulled from an external data source. It imported the time column as text (example: 07:30m or 12:00am). No space between. I've gotten it to work if I break it into 2 separate columns using the following 2 formulas: =TIMEVALUE(SUBSTITUTE(D2,"pm"," pm")) and =TIMEVALUE(SUBSTITUTE(D2,"am"," am")). Can anyone help so that it's one formula in one column? Please :)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
=REPLACE(A2,6,," ")+0

and then format as time
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,405
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try:
Code:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D2,"pm"," pm"),"am"," am"))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Not sure which of us you are referring too, but glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,523
Messages
5,523,370
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top