Date/INDIRECT Issues

I am pulling a date from a cell to another and I need to create a formula based off of the result.

This is the formula I am using to pull the date over
=MID(AA3,FIND("/",AA3)-2,10)

This is what I need to do to the result
=WORKDAY(M3,6)

I believe =INDIRECT should be used, but I'm having difficulty making it work.

Thanks in advance.

That works for me if AA3 contains a text string with a valid date in it, such as
"this is a date 10/15/2014 my friend"

In what way does it not work for you?
Do you get an error? What error?
Do you get the wrong result? What result did you actually get, and what result did you expect?

Can you post some examples of the contents of AA3 ?

I assume the first formula is in M3. It will return what looks like a date, but is in fact a text value that looks like a date. Change it to this to get a proper date:

=--MID(AA3,FIND("/",AA3)-2,10)

Then the other formula should work, adding on 6 working days to the date in M3. Ensure that both cells are formatted as a date.

Hope this helps.

Pete

@Pete

It works for me as is, without the text to number conversion.
Again, assuming the text string contains a valid date in the format of mm/dd/yyyy

I believe all the date functions (DAY, YEAR, MONTH, DATE, WORKDAY, NETWORKDAYS, EOMONTH etc..) will auto convert text to number whenever possible..

It wouldn't work for me, anyway, as we use a different date format. That might be what the problem is for the OP if s/he has:

"this is a date 15/10/2014 my friend"

in AA3.

Pete

Yep, I was thinking that too.

If the date in AA3 is in the format dd/mm/yyyy
Then the formula in M3 should be changed to

=DATE(MID(AA3,FIND("/",AA3)+4,4),MID(AA3,FIND("/",AA3)+1,2),MID(AA3,FIND("/",AA3)-2,2))

Thanks for all the help. Here is what I am trying to pull the dates from...I included AA3 and others so you can see the variations:

561-550-113 1/22

561-265-222 (922) DUE 1/22

561-728-216 (960) DUE 1/21

Jonmo: the formula worked perfectly, but the date came out wrong...I'm sure it's because the date format is different than what you were expecting. I wish I could tweak your formula to make it work for me, but I'm not that good. Is there anyway you can make it work for my date format?

Those samples don't make sense when compared to the formula you used.
=MID(AA3,FIND("/",AA3)-2,10)
That indicates you're pulling a 10 digit date as in mm/dd/yyyy or dd/mm/yyyy

Can you could post a sample book?

You are correct...I had to tweak it to

=MID(AA3,FIND("/",AA3)-2,4)

I was going to suggest

=TRIM(MID(AA3,FIND("/",AA3)-2,5))

You need 5 to account for 2 digit month + 2 digit day.

Well, 4 will only work if you have a 1-digit month, but if the date is always at the end you can do this:

=TRIM(RIGHT(AA3,5)&"/2013")

assuming your year is always the same, and that the day part is always 2 digits.

Please post some more examples.

Pete

Sorry I'm starting to confuse myself...the dates are in this format

561-550-113 1/22/2014

561-265-222 (922) DUE 1/22/2014

but where they are populating is in a 1/22 format.

ok, so then back to the 10, but add TRIM

=TRIM(MID(AA3,FIND("/",AA3)-2,10))

Okay, try this in M3:

=--RIGHT(AA3,10)

Format it how you want it to appear.

Do you have any examples of single-digit days? Do the days have leading zeros, even if the months don't?

Pete

Boom! Worked...Awesome...Thanks for the help!!!

You're welcome.

It worked! Thanks so much for the help!

I just ran into two more issues that I didn't think about...first not every cell in AA has a date, so it's populating #VALUE!...not the end of the world it's just annoying and secondly I need to have the date that was pulled from AA3 to be 3 workdays in advance when it populates into column M. Am I getting too crazy? If so, just let me know. Thanks

3 working days advanced from the date in AA

You can use the IFERROR function to handle the error.

=IFERROR(TRIM(MID(AA3,FIND("/",AA3)-2,10)),"")

Add 3 work days..
It seemed you already had a handle on the WORKDAY funciton.
Can't you just change the workday function you already have in another cell to 9 instead of 6 ?

The IFERROR worked perfectly, thanks! The workday issue I do have worked out for the other cells that will populate based on this one.

Below is what is going on. The Plan Assy Start is where the above formula is populating. The other columns build on themselves with =WORKDAY. So for the plan to be correct I need to add 3 workdays to the formula pulling from AA to populate the correct start date.

Plan Assy Start Plan Assy Finish Plan FT Finish Plan QA Finish Plan Ship
From AA +3 +1 +2 +1 +1

Damn it, that posted oddly...hold on

=iferror(workday(trim(mid(aa3,find("/",aa3)-2,10)),3),"")

These are columns, but it's not posting right so I will make them look like rows

Plan Assy Start
(From AA +3 working days)

Plan Assy Finish
(+1 working day from Plan Assy Start)

and so on...they build on each other. I have the others figured out because, well it's the easy part, but adding 3 working days to the already growing formula is above my ability.

Wow! That is awesome...works perfectly! Thank you so much...I might just get a raise outta this...I appreciate it!!!

You're welcome.

Originally Posted by liquidtrails

I might just get a raise outta this.

Where do you work? I'll send in my resume. J/K