Date/time format

[aotearoa]

I am receiving data with date and time in the following format:
2004-01-26, 03:32:47

When I enter this into excel, this pastes in one column. I want to be able to do some date/time calculations on this. eg difference between two numbers or subtract some hours from this number.

The format as it exists is clearly not correct for excel to do these calculations. In fact, I think excel just considers this to be a 'general' number. How should I go about getting this into the correct format.
I dont mind splitting the date and time into two columns or can leave it in one.

Tx

Stuart

 

[plettieri]

Hi:


The data is probably text....to convert DATA | TEXT TO NUMBERS for the range of interest on the menu bar..

When prompted---- delimiter...choose "," and "space" to correct the space that exists in your data.

hope this helps
plettieri

 

[Chitosunday]

You have to remove the comma to convert it to date and time format of excel

 

[nbrcrunch]

another solution is to split out the date & time into separate columns (data fields). If you are familiar with string and date/time functions you can accomplish this with those. For example, for the date part, the formula would be:

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))

This assumes that the data is in column A and starts at row 1.

 

[Yogi Anand]

Hi aotearoa:

As plettieri has stated the data is in text format. However because of the combination of date and time, I have a formula based approach to convert it into true date and time ...

y040129h1a.xls
	A	B	C	D
1
2	2004-01-26,03:32:47	01/26/20043:32:47
3
Sheet11

Would this do?

 

[rrdonutz]

Based on Yogi's example above, this would work just as well:

=--SUBSTITUTE(A2,",","")

with cell formatting applied the same way as in the example.

 

[Yogi Anand]

Hi Tom:

Good Call -- Short and Sweet -- I like that!