Date/time format

aotearoa

New Member
Joined
Jan 13, 2003
Messages
3
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 
Upvote 0

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 
Upvote 0

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
1
22004-01-26,03:32:4701/26/20043:32:47
3
Sheet11


Would this do?
 
Upvote 0

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
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.
 
Upvote 0

Forum statistics

Threads
1,186,371
Messages
5,957,473
Members
438,307
Latest member
bigmike1720

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top