Time out of a cell

I have column of data in the following format:

10/26/2015  1:16:41 PM

In the column next to it, I just want the time of day in military time. So it would just say 13:16:41

Thanks!

Chris
cansevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MPCP-MattCommented:
In the column where you want military time, try formatting it by selecting the number format drop down, going to "More Number Formats," selecting "custom," and then in the right pane selecting the format h:mm:ss

Once this is formatted, enter data into the cell as "=(reference cell)"
You can then drag the format down the entire column
You may need to apply a formula to not display text in cells that are referencing cells with no data

Please mark this as an answer if this works for you!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MPCP-MattCommented:
You can use the =IF(ISBLANK(referenced cell),"",referenced cell) formula and copy it down over the entire military column. It worked for me, now if the column with the date and time is empty, the military time cell shows empty. When the cell with date and time is not empty, the military time column shows military time.

Mark as solved if it works for you too!
0
cansevinAuthor Commented:
I need it only be the time... not just show the time. It is showing properly but in reality the date is still attached to it.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

MPCP-MattCommented:
I must have misunderstood you. So in the column on the left, you do not want the date associated, and the column on the right, just to be military?
0
MPCP-MattCommented:
When I put in what I described above, the military column is just time, with no date. Can you paste in a picture?
0
MPCP-MattCommented:
If you are trying to remove the date from the left column, you can format it as More Number Formats>Custom> h:mm AM/PM
0
Saurabh Singh TeotiaCommented:
Their are couple of things you can do..

1. Assuming you have value in a1..then ctrl+1 and change the display to time and select the appropriate time that you are looking for...However that will change the format only and the date will also be their..

2. If you want to remove date completely and just see the time then in B1 enter this formula...

=TIMEVALUE(TEXT(A1,"hh:mm:ss"))

Open in new window


Change the format to the necessary one and now you will see time only without date..

Saurabh...
0
Wayne Taylor (webtubbs)Commented:
As dates and times are stored as Double, with dates the whole numbers and times the fraction, strip out the whole date number leaving only the fraction using the MOD function...

    =MOD(A1,1)

...then format it as desired.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.