make calculation with mix format

[Dimitri]

Hello everyone

There is anypossibility to make calculations with a mix
data cell?

Ie i have in A1 : 06/2004 YTD
i want in B1 to see automatically by a formula one year
less (06/2003 YTD)

Is that possible?
Both cells have a General Format.

 

[Govind]

Hi,

Try this formula in B1

=TEXT(DATE(YEAR(LEFT(A1,FIND(" ",A1)))-1,MONTH(LEFT(A1,FIND("
",A1))),1),"mm/yyyy")&" YTD"

Regards

Govind.

 

[Frank Kabel]

Hi
if you always have the format MM/YYYY YTD try
=TEXT(DATE(MID(A1,4,4)-1,LEFT(A1,2),1),"MM/YYYY ""YTD""")

 

[Dimitri]

Thank you very much for your help
but ii does not work.

it gives wrong with the parenthesis. i try to delete or
add but it still do not work

 

[Dimitri]

Thank you very much for your help

but it gives me #VALUE
do i have to do something else?

 

[Govind]

Hi,

I guess this is what you are using.

=TEXT(DATE(YEAR(LEFT(A1,FIND(" ",A1)))-1,
MONTH(LEFT(A1,FIND(" ",A1))),1),"mm/yyyy")&" YTD"

The paranthesis is fine in this .

Regards

Govind.

 

[Dimitri]

Hi again
i am trying but still not work
can i sen dyou an email?

thanks

 

[Frank Kabel]

Hi
have you tried my formula. It should work

-----Original Message-----
Hi again
i am trying but still not work
can i sen dyou an email?

thanks
.

 

[Govind]

Hi,

Yes. You can email the spreadsheet to my mail id

Regards

Govind
(e-mail address removed)

 

[Govind]

Btw frank, is there anything wrong in my formula. I tried it myself and
it worked for me.

Any mistakes visible to your expert eyes ?

 

[Ron Rosenfeld]

Hello everyone

There is anypossibility to make calculations with a mix
data cell?

Ie i have in A1 : 06/2004 YTD
i want in B1 to see automatically by a formula one year
less (06/2003 YTD)

Is that possible?
Both cells have a General Format.

=TEXT(DATE(YEAR(DATEVALUE(LEFT(A1,FIND(" ",A1))))-1,
MONTH(DATEVALUE(LEFT(A1,FIND(" ",A1)))),1),"mm/yyyy ""YTD""")


--ron

 

[Frank Kabel]

Hi
maybe
=TEXT(DATE(YEAR(LEFT(A1,FIND(" ",A1)))-1,MONTH(LEFT(A1,FIND("
",A1))),1),"mm/yyyy")&" YTD"

The second part of the DATE function is:
MONTH(LEFT(A1,FIND(" ",A1)))

The LEFT function would return '06/2003' based on the OP's example
data. This may course problems for the OP. Though it worked for me
without a problem

 

[Govind]

Thanks frank for your observation.

Hi
maybe
=TEXT(DATE(YEAR(LEFT(A1,FIND(" ",A1)))-1,MONTH(LEFT(A1,FIND("
",A1))),1),"mm/yyyy")&" YTD"

The second part of the DATE function is:
MONTH(LEFT(A1,FIND(" ",A1)))

The LEFT function would return '06/2003' based on the OP's example
data. This may course problems for the OP. Though it worked for me
without a problem