excel: date time into Tick in 12HR format (not 24Hr)

Date                 Time
13_03_12        12_48_39
13_03_12        12_53_39
13_03_12        12_58_40
13_03_12        01_03_40
13_03_12        01_08_40
13_03_12        01_13_40
13_03_12        01_18_40
13_03_12        01_23_40
13_03_12        01_28_40

In regard to earlier question: "excel: date time into rick" (actually tick). I have made an error in formatting time into cvs, where provided equation (see attached) assumed 24Hr clock but I have use 12Hr clock.

What additional code/trick that convert time into 24Hr format (based on reading the date) and so I can calculate the tick second relate to row-1 as done in attached.

I have 3000 row from datalogger so far.......
Ticks.xls
Richard PayneChief Technology EngineerAsked:
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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

It's really hard to understand what you are referring to. Could you provide links, maybe?

>> In regard to earlier question: "excel: date time into rick" (actually tick)

You know the URL of that question, so why don't you just post it instead of having us search for it with our without typo in the title?

>> formatting time into cvs,

Do you mean CSV - "Comma Separated Values"? If not, what is "cvs"?

The formula in the file you attached does not do a good job of recognizing the date and time. Instead, in cell C1 try

=DATE(2000+RIGHT(A1,2)+0,LEFT(A1,2)+0,MID(A1,4,2)+0)+TIME(LEFT(B1,2)+0,MID(B1,4,2)+0,RIGHT(B1,2)+0)

copy down.

With regards to showing 24 hr versus AM/PM: that is just a matter of formatting the cell. The date/time value is an absolute number. You can choose how you want to display it. Hit Ctrl-1 to open the format dialog and have away in the numbers tab. Select from the existing formats or create your own custom format.

To see the date/time value with AM/PM and hiding the date, use custom format

h:mm:ss AM/PM

To see the time in 24 hr format use custom format

hh:mm:ss;@

This will only show the time part of the date/time value. You can combine it to show the date as well.

cheers, teylyn
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
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.