changing text formula?

biglb79

Active Member
Joined
Oct 17, 2007
Messages
294
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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:
Code:
=TEXT(EDATE(DATEVALUE(E5 & " 1, 2017"),1),"mmmm")
 
Upvote 0
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.
 
Upvote 0
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:
Code:
=TEXT(EDATE(DATEVALUE(E5 & " 1, 2017"),1),"mmmm")
You can also do it this way...
Code:
=TEXT(EOMONTH(1&E5,1),"mmmm")
 
Last edited:
Upvote 0
D3:
Code:
="YTD " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E3, "YTD ","") & " 1, 2017"),1),"mmmm"))

D4:
Code:
="AS OF " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E4,"AS OF ","")),1),"mmmm d, yyyy"))
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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:
Code:
="AS OF " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E4,"AS OF ",""))+1,1)-1,"mmmm d, yyyy"))
 
Upvote 0
thanks so much for both of your help! this is going to help me a ton when trying to record a roll forward macro
 
Upvote 0
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:
Code:
="AS OF " & UPPER(TEXT(EDATE(DATEVALUE(SUBSTITUTE(E4,"AS OF ",""))+1,1)-1,"mmmm d, yyyy"))
Challenge accepted :LOL:...
Code:
="AS OF "&UPPER(TEXT(EOMONTH(MID(E4,7,99),1),"mmmm d, yyyy"))

As for the YTD question...
Code:
="YTD "&TEXT(EOMONTH(MID(E4,7,99),1),"mmmm")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top