Solved

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

Posted on 2012-04-01
1
1,327 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:Richard Payne
1 Comment
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 37792839
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
location range 4 22
Hlookup formula help 14 19
Excel for Mac - How make those Tabs larger? 2 31
Excel:  Find text between 2nd hyphen and next space 10 30
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now