how to convert string into readable format for =DATEVALUE?

[lougs7]

Hi all,

please view attached workbook to understand what i'm talking about!
Sample Workbook.xlsx

I imported billing dates into my master file (column B), using logic in excel I broke up the string of the billing period to properly identify the Start and End dates. Now I would like to convert these strings in columns D and E into actual recognized dates in Excel so I can reference these dates in =netwokringdays and other formulas. I tried using the =DATEVALUE function but it returns a #VALUE error. I am not sure why because the format mm/dd/yyyy is one that the function should recognize.

can someone tell me how to convert columns D and E into Excel Dates using a formula, not manually? maybe it is the syntax that is wrong? maybe there is another formula that can be used? I am really stuck here and could use any help appreciate your time guys

incase you can't open the file here is a capture of the worksheet
Capture.PNG

[mcmahobt]

It appears as though Excel is already recognizing the values you have in columns D and E as dates. This:

Please Login or Register  to view this content.

entered into F3 returns 10, which is the number of network days between the two specified dates.

[lougs7]

I am getting the #VALUE when I try this.... I really don't know why I am always getting #value by doing this, I am going to try opening this file on another computer. Could it be something in the setting or anything? anytime I try touching these dates using readable format it doesn't work but I check online and it is supposed to work

[newdoverman]

You can convert all the dates in column B to real dates, separated into columns by selecting the dates from B2:B1211 then click on the Data Tab, Text to Columns, choose Fixed Width, click next, Delete the arrow between Final and Invoice, click Next, Select the first column in the window and select Date MDY, select the next column, click on Do Not Import, click on the next column and click on Date MDY, click on Finish.

Result all real dates:

	B	C	D
2	Billing Period
3	19/10/2014	01/11/2014	(Final Invoice)
4	19/10/2014	01/11/2014	(Final Invoice)
5	19/10/2014	01/11/2014	(Final Invoice)
6	19/10/2014	01/11/2014	(Final Invoice)
7	19/10/2014	01/11/2014	(Final Invoice)
8	19/10/2014	01/11/2014	(Final Invoice)
9	19/10/2014	01/11/2014	(Final Invoice)
10	19/10/2014	01/11/2014	(Final Invoice)
11	19/10/2014	01/11/2014	(Final Invoice)
12	19/10/2014	01/11/2014	(Final Invoice)
13	19/10/2014	01/11/2014	(Final Invoice)
14	19/10/2014	01/11/2014	(Final Invoice)
15	19/10/2014	01/11/2014	(Final Invoice)

[lougs7]

Turns out its my computer at work, I just tried now from home and im no longer getting an error. this is very strange at work my spreadsheet will say #VALUE in columns F & G all the way down

[newdoverman]

Mine gives the same error. The easy way I find is to use the Text to Columns and it rarely gives me trouble in converting dates when written in the way displayed in the example and I like not having formulae to make the conversion.