Solved

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

Posted on 2012-04-01
1
1,390 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 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