Excel Time column cannot be formatted

I have an excel spreadsheet with a time column that will not format. I want to combine the date and time columns. But the formula produces #VALUE!. I tried just formatting the time and it does not do it. Also, once you hide rows at the top, how do you unhide them?
qbjgqbjgConsultantAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
You need a space between the time and the "am" or "pm" for it to be recognised as a valid time, you can use this formula to combine date and time while also adding the space, i.e. in H6 I used

=B6+REPLACE(C6,LEN(C6)-1,0," ")

copied down

That's now a valid date/time that you can format any way you want, see attached

regards, barry
26896322.xlsx
0
 
qbjgqbjgConsultantAuthor Commented:
I am attaching the spreadsheet. test.xlsx. I thought maybe it was because the time has am pm in lowercase, so I replaced it with uppercase, but it still did not work.
0
 
TracyVBA DeveloperCommented:
Can you post your workbook?

>>Also, once you hide rows at the top, how do you unhide them?
Select all the cells (Ctrl + A) and click Format --> Row --> Unhide
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
qbjgqbjgConsultantAuthor Commented:
Thanks, that worked. What about unhiding columns at the top of the page?
0
 
TracyConnect With a Mentor VBA DeveloperCommented:
>>Thanks, that worked. What about unhiding columns at the top of the page?

See this comment http:35166007
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks. That resolved the issues. I really appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.