Make Formula into a UDF

[Stephen_IV]

Is it possible to make this formula into a UDF. We pull data out of a system at least 3 times a week where the date comes out consistanly wrong. I would like to have a UDF so that it would be easier for myself and my collegues. Thanks in advance!

Book1
	A	B	C	D
1	PPT DATE
2	24/03/2009	3/24/2009
3	29/05/2009	5/29/2009
4	21/05/2009	5/21/2009
5	9/6/2009	9/6/2009
6	28/05/2009	5/28/2009
7	20/05/2009	5/20/2009
8	19/05/2009	5/19/2009
9	7/5/2009	7/5/2009
10	11/2/2009	11/2/2009
11	12/5/2009	12/5/2009
12	20/05/2009	5/20/2009
13	22/04/2009	4/22/2009
14	6/3/2009	6/3/2009
15	21/04/2009	4/21/2009
16	2/4/2009	2/4/2009
17	20/05/2009	5/20/2009
18	5/6/2009	5/6/2009
19	6/5/2009	6/5/2009
Sheet1

 

[Jonmo1]

Honestly, a UDF wont' make it "easier"...
If anything, it's harder because you then have to ensure that macros are enabled.
Especially if the sheet is shared with multiple users...

If you already have a formula that works...stick with it.

If it can be done with a formula, then it probably should be done with a formula...

 

[VoG]

Agreed.

You could also try selecting the dates, Data > Text to Columns, click Next twice, check Date, select MDY then click Finish.

 

[shg]

Or maybe a different formula, since yours doesn't exchange the month and day for dates that come across as valid:

=IF(ISTEXT(A2), --(MID(A2,4,2) & "/" &LEFT(A2,2) & "/" & RIGHT(A2,4)), DATE(YEAR(A2), DAY(A2), MONTH(A2)))

 

[Jonmo1]

Or maybe a different formula, since yours doesn't exchange the month and day for dates that come across as valid:

=IF(ISTEXT(A2), --(MID(A2,4,2) & "/" &LEFT(A2,2) & "/" & RIGHT(A2,4)), DATE(YEAR(A2), DAY(A2), MONTH(A2)))

I was on the assumption that the posted formula intentionally did not swap the month/day on dates that were already dates.
the dates that are already dates (say A5 - 09/06/2009) are already correct.

 

[shg]

That's possible, but it would be a strange program that output data in that fashion.

 

[Stephen_IV]

Thanks fellows I figured that it would be pain and not worth it. shg the dates are messed up but that is the way that the program exports them. Thanks all!