Date Format

[jogrady]

I have a two noob questions I hope you can help me out with....

Situation: I've imported the following date string: 2008-09-25 16:10:34
When I try to change the column to date format it Excel 2007 and 2003 does not recognize this string a date with time stamp unless I remove the time stamp.

Questions 1: How do I get Excel to recognize that 2008 is year, 09 is month, etc down to seconds without modifying the string?

Question 2: If by chance I want to separate the time stamp from the date what is the best way to do it, and should it ever be done?

 

[VoG]

1. Select your dates, Data > Text to Columns, click Next twice and on the third screen tick Date and select YMD then click Finish.

2. Does this help

<b>Sheet7</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:143px;" /><col style="width:111px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">25/09/2008 16:10</td><td style="text-align:right; ">25/09/2008</td><td style="text-align:right; ">16:10:34</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=INT(A1)</td></tr><tr><td >C1</td><td >=A1-B1</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[jogrady]

Pure geniuses. Thanks for your help.

One clarification: Shouldn't it be A1+B1 as opposed to A1-B1?

 

[VoG]

It is A1-B1

A1 contains a floating point number (formatted as Date/Time) in which the integer portion represents the date (days since 1 January 1900) and the decimal portion represents the time (fraction of 24 hours).

In B1 we calculate the date as the integer portion of A1. Subtracting B1 from A1 gives the decimal portion of A1 which is the time.

You may need to format each of the cells in order to display the results as desired.

 

[jogrady]

Thanks again for all your help.

John