Converting Text to Date then using an IF formula

[pauldaddyadams]

Hi,

I have two dates on a file and I need an If formula to say If effective date is greater than inital date then "Yes", if not "NO".

My problem is the dates are in both text and USA format so I would need to convert them first.

Any ideas how?

[alansidman]

Multiply each of the dates by 1 and then apply the format you wish.

[pauldaddyadams]

Hi,

That doesnt work for the second date - get an #Value error

[alansidman]

Worked for me in the file you presented. Have you a different file that you working with?

[pauldaddyadams]

This is the actual file. The cell in yellow is the formula (the ones on the right is my attempt to get it to work - it doesnt)

Any ideas?

[pauldaddyadams]

In the example file above it appears that the dates are not being exported correctly - is there a work around for this?

[alansidman]

In the file you posted, there are no cells in yellow. Here is your file back with my change which works.

EDIT: Didn't see the second file. Looking at it now.

[alansidman]

I have looked at the new file. There is no formula in any of the cells in column P except for the first row. I am very confused as to what you want now. If I copy the formula down from row 2 it appears to give the required answer.

Alan

[pauldaddyadams]

Hi,

This still will not work for me and I dont know why?

I have reuploaded the file

[alansidman]

I think I got what's going on. Look at the attached.

[pauldaddyadams]

Hi

It works for most but rows 22 and 23 are wrong - I can not see why though?

[pauldaddyadams]

Hold fire as I think it might just be me reading the dates wrong!

[pauldaddyadams]

This is making me go mad. On the file you sent me if I go into the cells in G and hit return on some of them they error out.

Why is this?

[alansidman]

It worked fine for me. I think that the issue is the file is set up to US date standard and your machine is set for EU date standard. I've never been really good with sorting that stuff out as I never really needed to be concerned. You will have to figure out a workaround to either have your machine read it in US format or change the it somehow before you go any further.

[pauldaddyadams]

Thanks alansidman for looking into this.

I am going to leave it as open in the hope someone else knows. In the latest upload I need to try and elimiante the #Value errors, anyone know how (or even why)?

[alansidman]

My last shot at this, Paul.

This appears to do what you need.

http://stackoverflow.com/questions/4...pean-date-date

[pauldaddyadams]

Hi,

I have tried all the formulas in the link you provided and still nothing.

I still get errors no matter what formula I try on certain dates - doesnt anybody know why?

Paul

[Larry.LeBlanc@O]

When I format F:G as general, some of the entries still show as dates. When I delete those entries and re-enter them, this seems to correct the problem.

You must re-enter all dates that don't change to serial numbers when you format as general. I don't know why.

[XOR LX]

Hi,

Try this array formula**:

=IF(SUM(IFERROR(0+TEXT(F2:G2,"mm/dd/yyyy"),0+RIGHT(REPLACE(F2:G2,7,0,LEFT(F2:G2,3)),10))*{-1,1})<0,"Effective is After due","Effective is NOT After due")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[pauldaddyadams]

Hi,

I have tried that formula however it didnt work - I am unsure why exactly other than its reporting some dates before the inital when they are not.

[XOR LX]

Could you re-post your attachment with that formula included and highlight where you think the results is incorrect?

Regards