Auto Completion of Date Range, showing Month-Year Only

thommo100

New Member
Joined
Feb 28, 2005
Messages
3
Hi All,

I am new this board and relatively new to Excel - I was brought up on 1-2-3 (not by choice, a corporate decision by my employers).

Anyway, most things I can figure out but I am currently stuck with this slight problem.

I am developing a sheet that contains statistical data for a period of twelve months, back from from toda. I want to create a row of 12 cells showing the mmm/yy labels for each month/year of the last twelve months.

Is there a way to show a label for the current month, based on the current time/date settings, then for labels to the left of this cell to decrease incrementally for the twelve months period?

Hope I have made this clear.

Thanks

Dave
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
In the current-date cell, put..

=today()

and format the cell per your needs.

For succeeding months, reference the Today() cell +1

Example:
A1 contains =today()
B1 contains =DATE(YEAR(A1),MONTH(A1)+1,DAY(1))

Drag/copy B1 across whatever number of months you need.
 
Upvote 0
thommo100 said:
Hi All,

I am new this board and relatively new to Excel - I was brought up on 1-2-3 (not by choice, a corporate decision by my employers).

Anyway, most things I can figure out but I am currently stuck with this slight problem.

I am developing a sheet that contains statistical data for a period of twelve months, back from from toda. I want to create a row of 12 cells showing the mmm/yy labels for each month/year of the last twelve months.

Is there a way to show a label for the current month, based on the current time/date settings, then for labels to the left of this cell to decrease incrementally for the twelve months period?

Hope I have made this clear.

Thanks

Dave

B2:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

C2, copied across:

=EDATE(B1,-1)

Select B2:M2 and custom format this range (using Format|Cells) as:

mmm-yy
 
Upvote 0
Thanks for the immediate response!

I have just tried this but the incremental decrease is by day, not by month - I have also tried using today()-30, 60, 90 etc but depending on the current date, the month is not always correct.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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