Fill in months back automatically

[jonasmckee]

I am need of a way for the cells in row 1 to fill in the dates automatically backwards for 13 months. For example..
At cell N1 the user would put in a Date (3/15/02), then cells M1 backwards to A1 would then fill in the dates (M1 would equal N1 minus one month, L1 would equal N1 minus one month, etc.)
Any help would be aprrectiated please..

 

[gypo]

On 2002-03-28 23:51, Bob Umlas wrote:
MUCH easier way:
In N1 enter your date. Then right-click the fill handle and drag left to cell B1, for example. When you let go, you'll see a dropdown which enables you to choose "Fill Months". They will be backwards, as you wanted.

Or, if the date in N1 is changed often and you don't want to drag the cells each time,
this formula should do it (there could well be a simpler one) :-

=IF(N1=EOMONTH(N1,0),EOMONTH(EDATE(N1,-1),0),EDATE(N1,-1))
This message was edited by gypo on 2002-03-29 00:08

 

[Dave Hawley]

Hi gypo

It really depends on what you are determining as 1 month.

To use you eaxmple of:
30 November 2002

If you take 31 days you get
30/10/02 (same as EDATE)
If you take 30 days
you get 31/10/02 (same as what you say)

To put this another way, if you had
28/02/2001 in a cell and used the last day of the previous month (31/1/02) as being one month before, you are taking away 28 days, which means you are taking off the number of days for Feb and not really one month.

However, rather than just nit pick I will offer and alternative:

=IF(EOMONTH(A1,0)=A1,EOMONTH(A1,-1),EDATE(A1,-1))

 

[gypo]

On 2002-03-29 00:41, Dave Hawley wrote:
Hi gypo

It really depends on what you are determining as 1 month.

To use you eaxmple of:
30 November 2002

If you take 31 days you get
30/10/02 (same as EDATE)
If you take 30 days
you get 31/10/02 (same as what you say)

To put this another way, if you had
28/02/2001 in a cell and used the last day of the previous month (31/1/02) as being one month before, you are taking away 28 days, which means you are taking off the number of days for Feb and not really one month.

However, rather than just nit pick I will offer and alternative:

=IF(EOMONTH(A1,0)=A1,EOMONTH(A1,-1),EDATE(A1,-1))

Yes, that formula is a bit better.

Didn't think much of your arguments about what constitutes a month, though. One month is not a fixed number of days as you well know.

It seems to me that an end of a month is the last day of that month - can't really see how it can be something else.

 

[Jack in the UK]

This one easy...

enter in reqd cell the date and right click on bottom dright change to cross hold and drag on this now cross (filler handle) whatever its called. and drag and let go.

Box pops uo select as reqd.

suggest you choose as reqd loads to pick

no formulas or remember to do, its aleady there!

 

[P. Dante]

On 2002-03-29 05:33, Jack in the UK wrote:
This one easy...

enter in reqd cell the date and right click on bottom dright change to cross hold and drag on this now cross (filler handle) whatever its called. and drag and let go.

Box pops uo select as reqd.

suggest you choose as reqd loads to pick

no formulas or remember to do, its aleady there!

There's an echo on this board :-

On 2002-03-28 23:51, Bob Umlas wrote:
MUCH easier way:
In N1 enter your date. Then right-click the fill handle and drag left to cell B1, for example. When you let go, you'll see a dropdown which enables you to choose "Fill Months". They will be backwards, as you wanted.



Gypo replied :-

Or, if the date in N1 is changed often and you don't want to drag the cells each time,
this formula should do it (there could well be a simpler one) :-

=IF(N1=EOMONTH(N1,0),EOMONTH(EDATE(N1,-1),0),EDATE(N1,-1))


[ This Message was edited by: gypo on 2002-03-29 00:08 ]

 

[Dave Hawley]

Gypo

RE: Didn't think much of your arguments about what constitutes a month, though.

Then offer an alternative argument.


RE: One month is not a fixed number of days as you well know.

I don't recall ever saying it was.

RE: It seems to me that an end of a month is the last day of that month

Well of course it is, but what's your point. I think you are getting confused. The idea is to take 'one month' from consecutive dates, not automatically go to the last day of the preceeding month if the beginning month is the last day of that month.


RE: can't really see how it can be something else.

You really have to other some sort of logic for this line of thinking, you cannot just say, 'well, it just is'

When we put a date in a cell and take 'one month' off that date the 'month'we are going to take must be x number of days. If adding one month to say 31/Oct/02 using EDATE we get 30/NoV/02 , it has added 30 days (the number of days in Nov. the resulting month).

If we now put 30/Nov/02 in a cell and use EDATE to return a date one month BEFORE this date we get 30/Oct/02 , it has taken 31 days (the number of days in Oct. The resulting month again)

This same trend continues until you hit 30/Mar/02, the preceeding month is Jan (which only has 28 days) so EDATE can not use this resulting month because this would result in 2/Mar/02 and hence uses 30 days.

So as you can see I am certainly not saying "a month is a fixed number of days".

jonasmckee, may well want to always get the last day of the preceeding month if the beggining month is the last day of that month (hence my alternative), but technically this isn't returning the date one month preceeding consecutive months.





_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-29 15:10

 

[Yogi Anand]

Hi Bob Umlas:
Works Great! and no problem with the number of days in the respective month etc. either.

 

[gypo]

Dave Hawley

I still don't think much of your arguments.

It's really a matter of answering one simple question :-

If one month is added to 28 Feb, what result would any normal person expect?

I think the expected result would be 31 Mar.
Similarly, for other months where there are differences in the last day.

If you think some other result should be expected, then that's fine with me.

 

[Yogi Anand]

Hi Dave Hawley and Gypo:
How about if I suggest that each Function, like every tool has it s own special use. Some tools are better suited for some tasks than others. I am sure EDATE, EOMONTH, and EDIT|FILL|SERIES|... have their own place!

 

[Dave Hawley]

gypo, you may not think much of my argument (well it's microsofts really) but at least I am offering one.

If you also use the Fill handle method and choose- Series - Month -Step value -1 you will also get 30/Oct/2002 if the starting month is 30/Nov/02 (in other words the same logic applies), but after this you are taking 2 months from 30/Oct/02 then 3 etc. This is DIFFERENT to always taking one month off the preceding Month like the in the EDATE eaxmples.


No offence intended Gypo, but I suggest you contact Microsoft and frustate them with your heart felt argument.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-03-29 16:04
This message was edited by Dave Hawley on 2002-03-29 16:13