200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi,
I need to submit a report every month which is having data of previous month(i.e in the month of April ,I will have to submit report of March -2020).
I have attached a file having 3 sheets.These are having today's date by using today() function.
I want MARCH 2020 is to be written automatically wherever it is written.
I think this is achievable by substracting 1 from today's date.But i am not getting the exact formula/function.
You help is required.
Thank You
First of all, I suggest to designate one cell in your workbook as the source of your monthly date. I chose New!D1 and gave this cell the name RefDate. Now, when you start typing =Ref ... Excel will list RefDate as one of the functions available. Note that you will see a description when you click on it. That's the "Comment" you get access to when you select RefDate in the Name Manager and click Edit. Make sure the name's scope is the entire Workbook so that you can access it from all worksheets.
I gave this cell (New!D1, now aka RefDate) a value of TODAY()-27, with a nod to the 28 days of February. There are ways to directly set the month but they all involve more syntax. I think this is the easiest way to achieve what you want. And if, for reasons perhaps not knowable right now, you want to change this in the future, you will have to do it in only one place, to wit, New!D1, now aka RefDate, and that is the precise purpose of creating a named range in the first place, to save time in the future without extra effort now.
The use of RefDate is demonstrated in 'Covering Letter'!A11.
="... Text ... " & TEXT(RefDate, "mmmm yyyy")The main change I made was to integrate the date into the sentence. I also changed the alignment of the cell from Center to Left(Indent). The disadvantage of integration is that you can't format a partial cell. This I demonstrated in A7 where I bolded the branch name. Just select the text to treat in the Formula Bar and apply whatever font or colour you want. The change won't show in the Formula Bar but you will see it on the tab.
In 'Covering Letter New'!B3 I'm showing you another way of inserting RefDate. You will see that the formula in the cell is simply =RefDate. Look at the cell format. Of course, if you prefer the dash between month and year, this is where you would insert it.
"TIRTHPURI/MCR/" mmmm yyyyThe advantage of this method is that the text isn't part of the cell's value. You can still see the text but actually use the value in calculations.
In A12 and A14 you will find concatenations already explained above. However, I want to point out that the forced CR in A12 is produced with ALT+Enter.
You had A14 spread over 2 rows. I presume that you didn't find the way to make a text break into lines. It's the Wrap text property available on the Alignment tab of the Format Cells dialog.
I couldn't resist looking into your use of bullets. The character you chose isn't suitable because it isn't vertically centered. I chose a Wingdings2, character 151. Select the cell you want and place the cursor in the Formula Bar. From the Ribbon's Insert menu, select Symbols, select the Wingdings2 font and look for the symbol you want. Click Insert to insert it at the cursor point. The symbol will be added to the "recently used" where you can select it with one click next time.
Actually, if you just scroll the symbols without selecting a font you would come to the bullet Unicode character 2022 which is slightly smaller. Either way, this is a good example of having more than one font in a cell. I took your existing text, selected your symbol and inserted the new one on top of it. That was all of it. It wouldn't be so simple if you insert the symbol first and then try to continue typing expecting normal characters. Whatever you enter that way will come out in Wingdings2 font. So, please remember to add the bullet last.