Convert date field stored as a short text to date that will allow me to figure out number of days between

mcranda

New Member
Joined
Apr 17, 2013
Messages
19
I have data that I have to copy off of a website in order to get data for my boss. Unfortunately at this time the website does not allow me to export to excel or give me any type of report. Therefore, I have copied the data into an excel spreadsheet.

One of the columns is a date-time field (8/21/2014 12:56 PM). I believe this field is a short text as it appears as that when I import it into excel. This is where i am having problems. TExt to column doesn't change anything in this field. Changing the format type also doesn't change the information in this field. I want to be able to figure out how many days from today is the date in the column.

=today() - the date in spreadsheet - when I do this formula I get a #value

I have tried int(a2), left(a2, 10) and nothing seems to work

Using Microsoft 2013
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi - does this work to convert the text date?

=A2+0
 
Upvote 0
How about this?

=SUBSTITUTE(A2,CHAR(160),"")+0
 
Upvote 0
If you put this is a spare cell what does it return?

EDIT:
=CODE(RIGHT(A2,1))
 
Upvote 0
Can you upload a file with an example date that will not convert to a free file sharing site - like dropbox for example - and share the link here?

And can you confirm that your regional settings for dates are mm/dd/yyyy?
 
Upvote 0
What are you regional settings for dates? Is it not mm/dd/yyyy?

Excel Workbook
AB
1ModifiedConverted
28/21/2014 12:56 PM8/21/2014
38/12/2014 1:55 PM8/12/2014
44/28/2014 1:01 PM4/28/2014
512/3/2014 4:23 PM12/3/2014
612/3/2014 4:30 PM12/3/2014
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top