Hi
In a cell formatted as date (month-year) is it possible to:
i.e enter "12":
FORMULA BAR shows: Current Year-December-01 (since day not specified).
The CELL shows: Dec-Current year (2 digit is fine).
Thanks
Hi
In a cell formatted as date (month-year) is it possible to:
i.e enter "12":
FORMULA BAR shows: Current Year-December-01 (since day not specified).
The CELL shows: Dec-Current year (2 digit is fine).
Thanks
Last edited by drgkt; 01-05-2017 at 06:26 AM.
Hi
You could only do that in the same cell with a Sheet Change macro which personally I think would be OTT
Why not just enter the month number in a cell, say A1 and alongside in say B1 use the formula
Formula:Please Login or Register to view this content.
and Custom format B2 to "mmm yy"
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
I was looking not to add another column...
How will the macro work,
enter number, execute macro, go to next cell enter number, execute macro...?
Try in attached file first and then adapt this to suit your requirements
To illustrate, 3 ranges are automatically changed from month number to "month-year"
B2:B20 =2017 , E2:E20 = 2018, F2:F20 = 2016
Place VBA in the relevant sheet module
Please Login or Register to view this content.
Using Add-In A-Tools to view calendar (click to input date)
2017-01-03 13_10_09-Book1 - Excel.png
2017-01-03 13_10_32-Book1 - Excel.png
That's great Kev_!
Suppose the target range is the entire column A and the year is current year. How do I modify the code?
In VBA can things be "rem"ed out like in batch files? (Easier to modify the code by moving rems around instead of deleting and rewriting...)
Try this
All entries in Column A (below row 1) amended from month number to "month-2017"
Please Login or Register to view this content.
Thanks!
I guess NO REMs in VBA?
You are welcome
In vba, these 2 lines are the same. The apostrophe is a shortcut for Rem
If you are happy with your solution, please go to Thread Tools (top of thread) and mark the thread as solved.Please Login or Register to view this content.
thanks
One more question.
Is there a place in VBA Editor where I can store this as inactive and then drop it into a sheet when I need it?
There are a few ways to disable the macro.
One way
Add a button on the worksheet with this code behind it - it toggles events ON and OFF
Note that this enables/disables ALL event macrosPlease Login or Register to view this content.
Another way
(I would elect for this)
Add this as the first line of code in Private Sub Worksheet_Change
If OFF is entered in cell A1 (or your chosen cell) the macro exits without doing anythingPlease Login or Register to view this content.
You could have a choice of OFF and ON in the cell (using a dropdown)
Yet another way
(a bit of a "back of fag-packet" solution!)
Rename the macro with an X in front of the name
so "Private Sub Worksheet_Change.." , become "XPrivate Sub Worksheet_Change..."
and then it won't run
You must download Add-in A-Tools
Note: Close all office application before installing
Installation finish and open excel files. Any cells which is formated date can view as picture.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks