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

 

[Joe4]

This works for US regional systems:

If you have the text value “July” in cell E5, placing this formula in cell D5 will return August:

=TEXT(EDATE(DATEVALUE(E5 & " 1, 2017"),1),"mmmm")

 

[biglb79]

thanks, any chance you know how to change AS OF JULY 31, 2017 in cell E4 to AS OF AUGUST 31, 2107 in D4 and YTD July in E3 to YTD August in D3? I think I'll be good if I can just figure out those two formulas.

 

[Rick Rothstein]

This works for US regional systems:

If you have the text value “July” in cell E5, placing this formula in cell D5 will return August:

=TEXT(EDATE(DATEVALUE(E5 & " 1, 2017"),1),"mmmm")

You can also do it this way...

=TEXT(EOMONTH(1&E5,1),"mmmm")

 

[Joe4]

D3:

="YTD " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E3, "YTD ","") & " 1, 2017"),1),"mmmm"))

D4:

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

 

[Rick Rothstein]

thanks, any chance you know how to change AS OF JULY 31, 2017 in cell E4 to AS OF AUGUST 31, 2107 in D4

Are you always going for end of month to end of month? For example, what if you had FEBRUARY 28, 2017 in cell E4, what would you want in cell D4?

Also, is the value in E4 a real Excel date or just text typed that way?

 

[biglb79]

thanks so much, just one minor issue. for the as of date it says July 30 instead of July 31. I'm not sure how to fix that

 

[Joe4]

thanks so much, just one minor issue. for the as of date it says July 30 instead of July 31. I'm not sure how to fix that

I am sure that Rick will probably come up with a shorter version, but here is the change to make to my formula to account for that:

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

 

[biglb79]

thanks so much for both of your help! this is going to help me a ton when trying to record a roll forward macro

 

[Rick Rothstein]

I am sure that Rick will probably come up with a shorter version, but here is the change to make to my formula to account for that:

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

Challenge accepted ...

="AS OF "&UPPER(TEXT(EOMONTH(MID(E4,7,99),1),"mmmm d, yyyy"))

As for the YTD question...

="YTD "&TEXT(EOMONTH(MID(E4,7,99),1),"mmmm")