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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

thommo100

New Member
Joined
Feb 28, 2005
Messages
3
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
 

thommo100

New Member
Joined
Feb 28, 2005
Messages
3
Aladin,

Thanks very much mate, this works a treat.

A lot of frustration has just disappeared!

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,470
Members
414,145
Latest member
lonnie451

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
Top