changing text formula?

[biglb79]

Is there a formula that I can type to change the text July to August? Like I have July in cell E5 and when rolling forward I would want August in D5. Same question with changing AS OF JULY 31, 2017 to AS OF AUGUST 31, 2017.

Thanks in advance

 

[biglb79]

I have one last one that I could use help on. I thought I could figure it out on my own from the others, but it's proving to be a challenge.

AS OF JUNE 30, 2017 - YEAR-TO-DATE (is in cell E1) and I want July to be in D1

Thanks!

 

[Rick Rothstein]

AS OF JUNE 30, 2017 - YEAR-TO-DATE (is in cell E1) and I want July to be in D1

Give this formula a try...

=TEXT(EOMONTH(MID(LEFT(E1,FIND("-",E1)-1),7,99),1),"mmmm")

 

[biglb79]

Give this formula a try...

=TEXT(EOMONTH(MID(LEFT(E1,FIND("-",E1)-1),7,99),1),"mmmm")

that just showed "July"

 

[biglb79]

sorry, my post must have been misleading. I wanted it to say "AS OF JULY 31, 2017 - YEAR-TO-DATE"

 

[Rick Rothstein]

that just showed "July"

Well, that is what you asked for when you said...

AS OF JUNE 30, 2017 - YEAR-TO-DATE (is in cell E1) and I want July to be in D1

So, what do you actually want in cell D1 ?

 

[Rick Rothstein]

sorry, my post must have been misleading. I wanted it to say "AS OF JULY 31, 2017 - YEAR-TO-DATE"

Okay, give this a try...

="AS OF "&UPPER(TEXT(EOMONTH(MID(LEFT(E1,FIND("-",E1)-1),7,99),1),"mmmm d, yyyy"))&" - YEAR-TO-DATE"

 

[biglb79]

perfect! thanks so much!

 

[biglb79]

I'm trying to record a macro rolling forward our sheets and when I did for August it lists August 30, 2017 for this formula. is there something I can add to ensure it does August 31, September 30, October 31, November 30, etc... moving forward?

="AS OF " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E3,"AS OF ","")),1),"mmmm d, yyyy"))

 

[Rick Rothstein]

I'm trying to record a macro rolling forward our sheets and when I did for August it lists August 30, 2017 for this formula. is there something I can add to ensure it does August 31, September 30, October 31, November 30, etc... moving forward?

="AS OF " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E3,"AS OF ","")),1),"mmmm d, yyyy"))

Did you see the formulas I posted in Message #10 yet?

 

[biglb79]

ah ha, sorry. that works perfectly. I must have used the other formula when doing it