Need a subtrack formula with a text combination

[Manos]

Dear all
i need you valuable help for one more time

I have in cell A1 the value "06/2004 YTD"
i want in cell A2 with the help of a formula to subtrack
3 months and to be possible in A2 to have the
following "03/2004 YTD"

Any good formula for that?

Thanks in advance Manos

 

[Ron Rosenfeld]

Dear all
i need you valuable help for one more time

I have in cell A1 the value "06/2004 YTD"
i want in cell A2 with the help of a formula to subtrack
3 months and to be possible in A2 to have the
following "03/2004 YTD"

Any good formula for that?

Thanks in advance Manos

Here is one thought that avoids complicated formulas to parse out the date
portion:

1. In A1 insert 6/1/2004 (1 June 2004)

2. Format A1 & A2 as mm/yyyy "YTD"
Format/Cells/Custom/Number Type: mm/yyyy "YTD"

3. In A2, the formula: =DATE(YEAR(A1),MONTH(A1)+6,1)


--ron

 

[Manos]

Good tip Ron but
in the cell A1 i have 06/2004 YTD and that because it is
retrived from the system. I can not gibe it the format
you proposed.

Any other good ideas?

 

[Max]

Posted this suggestion in your other thread yesterday ..
---
Maybe a little klunky <g> but this seems to work ..

Try in A2:

=TEXT(IF(MONTH(LEFT(A1,SEARCH(" ",A1)-1))-3>0,MONTH(LEFT(A1,SEARCH("
",A1)-1))-3,IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3=0,12,IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3=-1,11,10)))&"/"&IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3>0,YEAR(LEFT(A1,SEARCH(" ",A1)-1)),YEAR(LEFT(A1,SEARCH("
",A1)-1))-1),"mm/yyyy")&" YTD"

---
Note: You have to remove the line wraps/breaks
(there would be several of these)
after pasting the above formula into A2,
and carefully re-instate the space (" ") in .. SEARCH(" ",A1)-1))-3 ...
(It's not a "")

With the value "06/2004 YTD" in A1,
A2 will return "03/2004 YTD"

If A1 contains the value "01/2004 YTD",
A2 will return "10/2003 YTD"

and so on ..

 

[Manos]

Good job Max but i can not manage to work it

Can you provide more help?

Regards Manos

 

[Max]

You're probably hit by the several line-wraps/breaks ..

Post a "readable" version of your email here and
I'll send you a sample workbook via private email

 

[Max]

Copy sent, Manos ..

 

[Ron Rosenfeld]

Good tip Ron but
in the cell A1 i have 06/2004 YTD and that because it is
retrived from the system. I can not gibe it the format
you proposed.

Any other good ideas?

1. This formula should work on the data as imported:

=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"m/yyyy ""YTD""")

If the formula does not work properly, there may be a leading <space> in the
data as retrieved "from the system". If that is the case, change the "LEFT"
function by substituting TRIM(A1) where you see (A1).

2. If you will be using the data in other areas of your worksheet, and doing
this repeatedly, it would be better to turn it into a real Excel date so that
other formulas can more easily refer to it. If this is required, post back. I
would perform that operation using a Macro to change the data in place.


--ron

 

[Ron Rosenfeld]

Maybe a little klunky <g> but this seems to work ..

Try in A2:

=TEXT(IF(MONTH(LEFT(A1,SEARCH(" ",A1)-1))-3>0,MONTH(LEFT(A1,SEARCH("
",A1)-1))-3,IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3=0,12,IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3=-1,11,10)))&"/"&IF(MONTH(LEFT(A1,SEARCH("
",A1)-1))-3>0,YEAR(LEFT(A1,SEARCH(" ",A1)-1)),YEAR(LEFT(A1,SEARCH("
",A1)-1))-1),"mm/yyyy")&" YTD"

Very klunky.

Simpler would be:

=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"m/yyyy ""YTD""")


--ron

 

[Max]

Very klunky ..

Agreed, no arguments ..

Simpler would be:

=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"m/yyyy ""YTD""")

Agreed, much^much better, Ron. Thanks
(I started on the wrong foot <g>)

Perhaps an extra "m" to sync the format desired: "mm/yyyy "

 

[Ron Rosenfeld]

Perhaps an extra "m" to sync the format desired: "mm/yyyy "

Yes. I missed the leading '0' in the first post.


--ron

 

[Ron Rosenfeld]

=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"m/yyyy ""YTD""")

As Max added, should be

=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"mm/yyyy ""YTD""")



--ron