Need a subtrack formula with a text combination

  • Thread starter Thread starter Manos
  • Start date Start date
M

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
 
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
Click to expand...

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
 
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?
 
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 ..
 
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
 
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?
Click to expand...

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
 
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"
Click to expand...

Very klunky.

Simpler would be:

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


--ron
 
Very klunky ..
Click to expand...

Agreed, no arguments ..
Simpler would be:

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

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

Perhaps an extra "m" to sync the format desired: "mm/yyyy "
 
=TEXT(DATE(MID(A1,FIND("/",A1)+1,4),
LEFT(A1,FIND("/",A1)-1)-3,1),"m/yyyy ""YTD""")
Click to expand...

As Max added, should be

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



--ron
 
Back
Top