Solved

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

Posted on 2012-04-01
1,315 Views
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
Question by:Richard Payne
1 Comment

LVL 50

Accepted Solution

teylyn 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

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…