[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1493
  • Last Modified:

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
0
Richard Payne
Asked:
Richard Payne
1 Solution
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now