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..

 

[Legacy 1363]

For what it's worth, I go with Gypo's logic.

However, the formula =IF(EOMONTH(N1,0)=N1,EOMONTH(N1,-1),EDATE(N1,-1)) does not produce the correct results in all instances.

If the start date in N1 is the 28th or 29th day of a month other than Feb, once Feb is reached the dates thereafter will show the last day of the month (which would be wrong).

The following formula would overcome this :-
=IF(EOMONTH($N1,0)=$N1,EOMONTH($N1,-COLUMN()+14),EDATE($N1,-COLUMN()+14))

My approach, however, would be to do it as B. Umlas suggested, or if it had to be done regularly, do it with an event procedure :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$1" Then
[N1:AA1].DataSeries Date:=xlMonth, Step:=-1
End If
End Sub
This message was edited by C. O. Jones on 2002-03-29 16:26

 

[Dave Hawley]

Hi C. O. Jones

I too may "go with Gypos logic" but he doesn't really have any! He just keeps saying that is what it should do without offering any logic.

You have to realise that using the Fill Handle is NOT the same as using a formula method. This is simply because the Fill Handle with the Series|Step -1|Months is using the first month as the month to take from all the time. Where using a formula will take from Oct then Sep, then Aug etc

If the numbers (which all dates are) were Linear it would not matter, but this does apply with dates. The method would work the same if it were only numbers, eg 10 and take one each step.


However having said all this, I will again say that, this maybe what is needed as it is a common perception.

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

This message was edited by Dave Hawley on 2002-03-29 17:21

 

[Legacy 1363]

Dave Hawley

I really do not have to defend Gypo's stance, but as I said that I go with his/her logic while you've said he/she hasn't offered any, I shall make a brief response :-

"If one month is added to 28 Feb, what result would any normal person expect?
I think the expected result would be 31 Mar."

As far as I'm concerned, the above is a logical offering.

"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."

This statement is also logical and does not have to be backed up with arguments that you say should be offered by Gypo - it is a self-evident fact.

I don't intend entering into further postings on this subject.
Date anomalies will always exist because of months' varying lengths and they will not be resolved however long they are discussed.

By way of illustrating this, you stated in an earlier post that "if you had
28/02/2002 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."

Well .... the number of days in Feb 02 happens to be 28 (fact, not logic) and so, like it or not, this particular period of 28 days is one month (also fact). For the particular example you have chosen, it could also logically be argued (more logically in my opinion) that if your definition of one month were to be used, one month before 28/2/02 would be 28/1/02. But is this not one month and 3 days - that is, the whole of the month of Feb plus 3 days in Jan?

I'm sure you see the point - there is no resolution to this sort of question.
Can't imagine why such frenzied postings have arisen about this.
This message was edited by C. O. Jones on 2002-03-29 21:17

 

[Dave Hawley]

Hi C. O Jones

RE:Can't imagine why such frenzied postings have arisen about this.

Don't look at it this way, it's all about all of us sharing our thoughts and opinions and enjoying doing so!

I am not wishing for an agument here, but

"If one month is added to 28 Feb, what result would any normal person expect?
I think the expected result would be 31 Mar."


Is nothing more than a statement and conveys no logical offering at all.


"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."

It very much appears you have not read my previous response to this ie: I agree! But this grand statement has nothing to do with the debate. In other words it has nothing to do with taking 1 month from a starting month then one month of the resulting month and so on.. I feel this where you are both getting confused.

=EDATE("28/02/02",1) Results in 28/03/02 this is 28 days, the number of days in Feb

=EDATE("28/2/02",-1) Results in 28/01/02 this is 31 days, the number of days in Jan

It appears I may have typed the oppsosite before (my mistake) .
The fact is, it works one way going forward (uses days in the Start month) and reverse (uses uses days in the resulting month) going backwards. This is Microsofts logic and makes sense to me, I fail to see how you cannot see this.

Using the last day of the month to say you MUST automatically go the last day of the next month might 'sound like the correct way' but it is not logical and thus cannot be used going backwards.


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

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

 

[Legacy 1363]

I had intended to withdraw from this nonsense but can't leave some of your comments unanswered :-

Your post :
"If one month is added to 28 Feb, what result would any normal person expect?
I think the expected result would be 31 Mar."
Is nothing more than a statement and conveys no logical offering at all.

Response :
Well ... if you think that the quoted statement is not logical then what would be? To me, the statement is an entirely self explanatory piece of logic - surely it is logical to state that adding one month to the last day of a month should take you to the last day of the following month.
If that is not logical, then it logically follows that adding one month to the last day of a month should NOT take you to the last day of the following month - which would be nonsense.
What exactly is your claimed logical offering - you are rejecting Gypo's logical offering without offering one of your own, which is what you accused him of doing.

Your post :
"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."

It very much appears you have not read my previous response to this ie: I agree! But this grand statement has nothing to do with the debate. In other words it has nothing to do with taking 1 month from a starting month then one month of the resulting month and so on.. I feel this where you are both getting confused.

Response :
You say "this grand statement has nothing to do with the debate....has nothing to do with taking 1 month from a starting month then one month of the resulting month and so "

What?
The statement happens to be the whole crux of the debate.
The debate started because of the anomalies that arise if the start date happens to be the last day of a month.

Also, you say "I feel this is where you are both getting confused."
My friend, your feeling is incorrect - I am certainly not confused.
It's strange that you should mention it, however, since I have the distinct impression that the confusion lays at your door.


Your post :
=EDATE("28/02/02",1) Results in 28/03/02 this is 28 days, the number of days in Feb

Response :
So if someone was born on 28/02/02, when would you celebrate his 1 month birthday? On 28/03/02? Is that logical?

Your post :
Using the last day of the month to say you MUST automatically go the last day of the next month might 'sound like the correct way' but it is not logical and thus cannot be used going backwards.

Response :
You say "it is not logical". Why? I have explained my logic. Where is your logical offering for dismissing this without explanation?
Having said that, anomalies are always going to arise whatever way you want to look at the subject.
Strange results can arise both from EDATE and from the formula I suggested as an alternative.
It's really a question of knowing that they exist so that one is in a position of being able to accommodate them.
For example, merely using EDATE as in your examples, if the start date is 31/01/02 then =EDATE("31/01/02",1) results in 28/02/02. If it is required to take the result and change it back to the start date, =EDATE("28/02/02",-1) doesn't do it - it produces 28/01/02.


cojones

 

[Dave Hawley]

You missing some very important points here. the word is SUBTRACT not add as you keep stating.

You cannot simply make a statement that sounds logical and then say it simply is because it sounds that way. That is childish. For example "if I throw this spear directly at the fish I see in the water I will hit it!" That's sounds logical doesn't it? But any person will tell you that it doesn't work that way.


You have offered nothing to this debate except to say your way sounds logical, I give up with assurance that I am correct.

Feel free to have the last word.

 

[Jack in the UK]

Hi guys

bar the silly banta flying about i see all sides.

But i would like bar comments saying things are no so, some back up to guive evidence to that comment. i have learnt such a lot from them posts and thank all that have done so.

But i will say i posted a simple issue which is default Microsoft, and if it wron them that where we need to help and fix. Dave pointed out subtract, which my simpl;e idea and Dave more consince ideas work. the idea is to give definitave answer or opinions and offer ideas, this we have done!

Ive tested Dave formula and agree work fine to me so regardless that answered the question, these no more leg work on this one bar inprovements or some twist to the formula or mabe some UDF someone might post.

All in all good fun guys cheers for that.
(Remember me reading and writting ant so good and even english i am is not very good so sorry if me explain bad some times, good fun guys!)

 

[Legacy 1363]

On 2002-03-30 00:19, Dave Hawley wrote:
You missing some very important points here. the word is SUBTRACT not add as you keep stating.

You cannot simply make a statement that sounds logical and then say it simply is because it sounds that way. That is childish. For example "if I throw this spear directly at the fish I see in the water I will hit it!" That's sounds logical doesn't it? But any person will tell you that it doesn't work that way.


You have offered nothing to this debate except to say your way sounds logical, I give up with assurance that I am correct.

Feel free to have the last word.

OK. Thanks. I'll have the last word :-

"You missing some very important points here. the word is SUBTRACT not add as you keep stating."
No, it is you that's missing the point.
Whether a month has to be added or subtracted makes no difference to the fact that in certain circumstances, anomalies will arise because all months do not have the same number of days - WHATEVER METHOD IS USED.

"You cannot simply make a statement that sounds logical and then say it simply is because it sounds that way. That is childish."

I have not once claimed such a thing. Rather it has been you that has been making such claims of your own assertions without explanation, and also dismissing suggestions from others on the grounds of illogicality without providing reasons.
You also failed to specify which particular statements were made by others that were not backed by reasoned argument.

The words "because it sounds that way" are yours - I have said no such thing.
I have made a few statements of fact that you say are illogical - for example, adding one month to (or subtracting from, if you wish) a date which is the last date in a month should produce the last day of the following month (or the previous month, if subtracting). This is not some weird illogical concept - I illustrated the natural sense of the statement by asking whether you would hold a one-month birthday celebration for someone born on 28/02/02, on 28/03/02 or 31/03/02.
You have chosen not to respond to the birthday question but instead have given some completely inappropriate irrelevant hypothetical illustration which bears nothing in common with anything that has been discussed.

You also do yourself no credit by making comments such as "That is childish." and "You have offered nothing to this debate"

Thanks for affording me the last word.

 

[gypo]

WOW! I've just tuned in again.
Don't you think you guys have gone a bit over the top with this one.
The question was only about subtracting one month from a date for God's sake!

 

[Tessa Tickles]

TO : C.O.JONES

Great name! Family Spanish attachments?