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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
You have to remove the comma to convert it to date and time format of excel
 

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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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?
 

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,896
Messages
5,766,992
Members
425,392
Latest member
Booknerd

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