Months Months Months

Months Months Months

Post by Ed » Wed, 07 Mar 2001 02:35:56



Someone asked me if there was an easy way to calculate elapsed months from
two dates. My first response was, "Sure, that's easy." Then I proceeded to
prove myself wrong. Maybe it's Monday brain fade, but the answer is eluding
me.

I've tried this:

  200103 (Mar 2000)
- 199611 (Nov 1996)
--------
     492

Obviously wrong. The answer in this case is 52 months isn't it? I've never
had to do this calc before. The answer must be accurate to the day, so
approximating won't work. If the start date is near a month border, the calc
must detect it.

Thanks, group.

Ed

 
 
 

Months Months Months

Post by Kevin Powi » Wed, 07 Mar 2001 03:27:40




Quote:>I've tried this:

>  200103 (Mar 2000)
>- 199611 (Nov 1996)
>--------
>     492

>Obviously wrong. The answer in this case is 52 months isn't it?

Based on the routine I wrote, the answer is 40 months, but I'm using
the idea that a month is considered elapsed once you move from one
month to the next, not how many days difference there is between two
dates:

01 Jan 01 to 01 Feb 01 = 1 Month
31 Jan 01 to 01 Feb 01 = 1 Month
01 Jan 01 to 31 Dec 01 = 11 Months
01 Jan 01 to 01 Jan 02 = 12 Months

Is this the right idea?

Kevin Powick
kpowick[at]on[dot]aibn[dot]com

 
 
 

Months Months Months

Post by Richard A. Wilso » Wed, 07 Mar 2001 03:49:41


how about

((2000-1996)*12) + (03-11) = 40

does the day of the month matter?


> Someone asked me if there was an easy way to calculate elapsed months from
> two dates. My first response was, "Sure, that's easy." Then I proceeded to
> prove myself wrong. Maybe it's Monday brain fade, but the answer is eluding
> me.

> I've tried this:

>   200103 (Mar 2000)
> - 199611 (Nov 1996)
> --------
>      492

> Obviously wrong. The answer in this case is 52 months isn't it? I've never
> had to do this calc before. The answer must be accurate to the day, so
> approximating won't work. If the start date is near a month border, the calc
> must detect it.

> Thanks, group.

> Ed

--
Richard A. Wilson
Lakeside Systems
Smithfield, RI, USA


www.lakeside-systems.com

 
 
 

Months Months Months

Post by Ed » Wed, 07 Mar 2001 04:25:39


My brain fade is worse than I thought. (I guess that if I HAVE brain fade, I
wouldn't be able to know WHAT I thought). But anyway, my example was 200103
but I labeled it Mar 2000. But The solution below seems to work. So, the
formula would be:

(END.YEAR - BEG.YEAR) + ABS(END.MONTH - BEG.MONTH)

Do I have it right?

Ed



| how about
|
| ((2000-1996)*12) + (03-11) = 40
|
| does the day of the month matter?
|
| >
| > Someone asked me if there was an easy way to calculate elapsed months
from
| > two dates. My first response was, "Sure, that's easy." Then I proceeded
to
| > prove myself wrong. Maybe it's Monday brain fade, but the answer is
eluding
| > me.
| >
| > I've tried this:
| >
| >   200103 (Mar 2000)
| > - 199611 (Nov 1996)
| > --------
| >      492
| >
| > Obviously wrong. The answer in this case is 52 months isn't it? I've
never
| > had to do this calc before. The answer must be accurate to the day, so
| > approximating won't work. If the start date is near a month border, the
calc
| > must detect it.
| >
| > Thanks, group.
| >
| > Ed
|
| --
| Richard A. Wilson
| Lakeside Systems
| Smithfield, RI, USA
|

| www.lakeside-systems.com

 
 
 

Months Months Months

Post by Bill McKenz » Wed, 07 Mar 2001 04:25:34


Are you losing your touch my friend or are you just joking around?
 
 
 

Months Months Months

Post by Richard A. Wilso » Wed, 07 Mar 2001 04:39:30


try this

((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
((2001 - 1996)*12) + (03-11)
      (5*12) + (-8)
        60   - 8 = 52


> My brain fade is worse than I thought. (I guess that if I HAVE brain fade, I
> wouldn't be able to know WHAT I thought). But anyway, my example was 200103
> but I labeled it Mar 2000. But The solution below seems to work. So, the
> formula would be:

> (END.YEAR - BEG.YEAR) + ABS(END.MONTH - BEG.MONTH)

> Do I have it right?

> Ed



> | how about
> |
> | ((2000-1996)*12) + (03-11) = 40
> |
> | does the day of the month matter?
> |

> | >
> | > Someone asked me if there was an easy way to calculate elapsed months
> from
> | > two dates. My first response was, "Sure, that's easy." Then I proceeded
> to
> | > prove myself wrong. Maybe it's Monday brain fade, but the answer is
> eluding
> | > me.
> | >
> | > I've tried this:
> | >
> | >   200103 (Mar 2000)
> | > - 199611 (Nov 1996)
> | > --------
> | >      492
> | >
> | > Obviously wrong. The answer in this case is 52 months isn't it? I've
> never
> | > had to do this calc before. The answer must be accurate to the day, so
> | > approximating won't work. If the start date is near a month border, the
> calc
> | > must detect it.
> | >
> | > Thanks, group.
> | >
> | > Ed
> |
> | --
> | Richard A. Wilson
> | Lakeside Systems
> | Smithfield, RI, USA
> |

> | www.lakeside-systems.com

--
Richard A. Wilson
Lakeside Systems
Smithfield, RI, USA
Voice 401-231-3959
Fax   401-231-3943      

www.lakeside-systems.com
 
 
 

Months Months Months

Post by Mike Proct » Wed, 07 Mar 2001 04:43:08


Subtract the dates in internal format for
the number of days and divide by 30.2.

Mar 2000 = 11749
Nov 1996 = 10553

= 1196 days/30.2
= 39.6 months


Quote:>Someone asked me if there was an easy way to calculate elapsed months
>from two dates. My first response was, "Sure, that's easy." Then I
>proceeded to prove myself wrong. Maybe it's Monday brain fade, but the
>answer is eluding me.

>I've tried this:

>  200103 (Mar 2000)
>- 199611 (Nov 1996)
>--------
>     492

>Obviously wrong. The answer in this case is 52 months isn't it? I've
>never had to do this calc before. The answer must be accurate to the
>day, so approximating won't work. If the start date is near a month
>border, the calc must detect it.

>Thanks, group.

>Ed

 
 
 

Months Months Months

Post by Luke Webbe » Wed, 07 Mar 2001 06:34:41



Quote:> Someone asked me if there was an easy way to calculate elapsed months from
> two dates. My first response was, "Sure, that's easy." Then I proceeded to
> prove myself wrong. Maybe it's Monday brain fade, but the answer is
eluding
> me.

> I've tried this:

>   200103 (Mar 2000)
> - 199611 (Nov 1996)
> --------
>      492

Heh. Effectively, you're multiplying the year by 100 and then adding the
month to it before subtracting one from the other. Plainly the correct
number to multiply by is 12, *not* 100. See the other porst in this thread
for more correct answers.

Quote:> Obviously wrong. The answer in this case is 52 months isn't it? I've never
> had to do this calc before. The answer must be accurate to the day, so
> approximating won't work. If the start date is near a month border, the
calc
> must detect it.

Cheers,
Luke
 
 
 

Months Months Months

Post by David L Weissma » Wed, 07 Mar 2001 23:27:01



> Someone asked me if there was an easy way to calculate elapsed months from
> two dates. My first response was, "Sure, that's easy." Then I proceeded to
> prove myself wrong. Maybe it's Monday brain fade, but the answer is eluding
> me.

> I've tried this:

>   200103 (Mar 2000)
> - 199611 (Nov 1996)
> --------
>      492

> Obviously wrong. The answer in this case is 52 months isn't it? I've never
> had to do this calc before. The answer must be accurate to the day, so
> approximating won't work. If the start date is near a month border, the calc
> must detect it.

> Thanks, group.

> Ed

I don't know of any easy way but here is a method that should work.

1. Multiply the 4 digit year by 12.

2. Add the number of the month to the results of (1)

3. Do this for both start and stop dates.

4. Subtract the start number from the stop number. If the stop day is less than
the start day you may want to subtract 1 from the results.

Hope this helps.

 
 
 

Months Months Months

Post by Ed » Fri, 09 Mar 2001 02:23:54


Thanks to all for your help. My son even chipped in with a mathematical
approach which actually works, but I settled on this:

(start_year * 12) - (end_year * 12) + (start_month - end_month)

If the start_month value is smaller than the end_month, the result will be
negative, and  adding it to the years will still produce the right numbers.
Then doing an ABS() will give the months without regard to starting/ending
dates.

Here's code:

 001 10 CRT 'ENTER START DATE ':
 002 INPUT BEG.DATE
 003 IF BEG.DATE = '' THEN STOP
 004 BEG.DATE = ICONV(BEG.DATE,'D')
 005 CRT 'ENTER END DATE ':
 006 INPUT END.DATE
 007 END.DATE = ICONV(END.DATE,'D')
 008 BEG.YEAR = OCONV(BEG.DATE,'D4/')[7,4]
 009 END.YEAR = OCONV(END.DATE,'D4/')[7,4]
 010 BEG.MONTH = OCONV(BEG.DATE,'D2/')[1,2]
 011 END.MONTH = OCONV(END.DATE,'D2/')[1,2]
 012 MONTHS = ABS(((BEG.YEAR - END.YEAR) * 12) + (BEG.MONTH - END.MONTH))
 013 CRT 'MONTHS = ':MONTHS
 014 CRT
 015 GO 10

Ed



|
|
|
| > Someone asked me if there was an easy way to calculate elapsed months
from
| > two dates. My first response was, "Sure, that's easy." Then I proceeded
to
| > prove myself wrong. Maybe it's Monday brain fade, but the answer is
eluding
| > me.
| >
| > I've tried this:
| >
| >   200103 (Mar 2000)
| > - 199611 (Nov 1996)
| > --------
| >      492
| >
| > Obviously wrong. The answer in this case is 52 months isn't it? I've
never
| > had to do this calc before. The answer must be accurate to the day, so
| > approximating won't work. If the start date is near a month border, the
calc
| > must detect it.
| >
| > Thanks, group.
| >
| > Ed
|
| I don't know of any easy way but here is a method that should work.
|
| 1. Multiply the 4 digit year by 12.
|
| 2. Add the number of the month to the results of (1)
|
| 3. Do this for both start and stop dates.
|
| 4. Subtract the start number from the stop number. If the stop day is less
than
| the start day you may want to subtract 1 from the results.
|
| Hope this helps.
|

 
 
 

Months Months Months

Post by Kevin Powi » Fri, 09 Mar 2001 03:05:57


Ed,

You don't need the ABS() function because the answer will always be
positive.

The solution presented by Richard Wilson (below) proves this.

((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
((2001 - 1996)*12) + (03-11)
      (5*12) + (-8)
        60   - 8 = 52

The end month will only be less than the beginning month if the dates
span two different years.  If this happens, then you could end up
adding a negative number from the months calculation no less than -11
(1-12) to the result of the year calculation which would be no less
than 12 ((2001-2000)*12).  The smallest number you would ever see is
1. i.e Dec 2000 to Jan 2001

Kevin Powick
kpowick[at]on[dot]aibn[dot]com

 
 
 

Months Months Months

Post by Homer L. Haze » Fri, 09 Mar 2001 04:07:30


Kevin,

You certainly know how to make your software fool proof.  But, when you are
dealing with users - and if there are any looking, please forgive me for
what I am about to say, it's hard to make your software "DAMN FOOL" proof.

Most users are wonderful and would never dream of putting the wrong date
first, but perhaps one out of ten million or so will put the dates in the
wrong order - thus you would need the absolute to take care of the problem.

Larry Hazel


Quote:> Ed,

> You don't need the ABS() function because the answer will always be
> positive.

> The solution presented by Richard Wilson (below) proves this.

> ((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
> ((2001 - 1996)*12) + (03-11)
>       (5*12) + (-8)
>         60   - 8 = 52

> The end month will only be less than the beginning month if the dates
> span two different years.  If this happens, then you could end up
> adding a negative number from the months calculation no less than -11
> (1-12) to the result of the year calculation which would be no less
> than 12 ((2001-2000)*12).  The smallest number you would ever see is
> 1. i.e Dec 2000 to Jan 2001

> Kevin Powick
> kpowick[at]on[dot]aibn[dot]com

 
 
 

Months Months Months

Post by Richard A. Wilso » Fri, 09 Mar 2001 04:17:17


If this was a data entry situation I guess you could add errors messages
concerning beg/end date being incorrect. But I would guess the biggest
problem would be that silly num lock key some users like to fiddle with.
The latter problem can be solved with the use of a small*driver
and removal of all keys that are not alpha or numeric (I once had do
perform this operation)

Rich


> Kevin,

> You certainly know how to make your software fool proof.  But, when you are
> dealing with users - and if there are any looking, please forgive me for
> what I am about to say, it's hard to make your software "DAMN FOOL" proof.

> Most users are wonderful and would never dream of putting the wrong date
> first, but perhaps one out of ten million or so will put the dates in the
> wrong order - thus you would need the absolute to take care of the problem.

> Larry Hazel



> > Ed,

> > You don't need the ABS() function because the answer will always be
> > positive.

> > The solution presented by Richard Wilson (below) proves this.

> > ((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
> > ((2001 - 1996)*12) + (03-11)
> >       (5*12) + (-8)
> >         60   - 8 = 52

> > The end month will only be less than the beginning month if the dates
> > span two different years.  If this happens, then you could end up
> > adding a negative number from the months calculation no less than -11
> > (1-12) to the result of the year calculation which would be no less
> > than 12 ((2001-2000)*12).  The smallest number you would ever see is
> > 1. i.e Dec 2000 to Jan 2001

> > Kevin Powick
> > kpowick[at]on[dot]aibn[dot]com

--
 
 
 

Months Months Months

Post by Ed » Fri, 09 Mar 2001 05:04:37


The only reason I added ABS() was to allow me to ignore dates. This code
will likely be used in a batch process. The span of months is important in
this case, not the beginning/ending dates. So I only wanted to avoid a -52
from inadvertently creeping in where a 52 was wanted.

Ed


| Ed,
|
| You don't need the ABS() function because the answer will always be
| positive.
|
| The solution presented by Richard Wilson (below) proves this.
|
| ((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
| ((2001 - 1996)*12) + (03-11)
|       (5*12) + (-8)
|         60   - 8 = 52
|
| The end month will only be less than the beginning month if the dates
| span two different years.  If this happens, then you could end up
| adding a negative number from the months calculation no less than -11
| (1-12) to the result of the year calculation which would be no less
| than 12 ((2001-2000)*12).  The smallest number you would ever see is
| 1. i.e Dec 2000 to Jan 2001
|
|
| Kevin Powick
| kpowick[at]on[dot]aibn[dot]com

 
 
 

Months Months Months

Post by Mike Proct » Fri, 09 Mar 2001 05:35:56


Quote:>You don't need the ABS() function because the answer will always be
>positive.

>The solution presented by Richard Wilson (below) proves this.

>((END.YEAR-BEG.YEAR)*12) + (END.MONTH-BEG.MONTH)
>((2001 - 1996)*12) + (03-11)
>      (5*12) + (-8)
>        60   - 8 = 52

This is easier than subtacting the internal
date format (# of days) and dividing by 30.2???

12114 3/1/01
10533 11/1/96
-----
1581
/30.2
=52.35

 
 
 

1. Last Day of Month, End of Month, First Day of Month

I searched all over google for answers to this question and came across many
solutions. But converting dates to character and plugging a "01" in and
putting the date back together seemed to old school.

Ex..



Or maybe it had a different purpose that I didn't catch in the thread. But
tinkering with Healthcare data has caused me to tinker with dates more than
I could care for.

I figure I would just post this incase someone else needed a simple solution
that didn't eat up the runtime of the query.

/*CurrentMonth is set to the first day of the month*/


/*Figure out the last day of the current month */


/*Figure out the last day of the previous month */


Not to mention storing all of these little bits of code in the newsgroup
allows me to find them in the future from anywhere.

2. Clustered Indices

3. Outputting days of the month for each month

4. How to see SQL sent to server

5. Rolling-up Past Month figures to current Month

6. JDK 1.2.2, RedHat 7.1, Oracle 8.1.7.3

7. Difficult current month last month comparison and ParallelPeriod

8. Transactional Replication : log reader agent fails (SQL2K SP2 WIN2K SP2)

9. Comparing the current months figures to the same time last month

10. MDX Question - generating data for last 3 months or last 12 months

11. Calculated Member for Getting Current Month and Previous Year Same Month

12. DATEADD(month, ...) into a short month

13. Converting month numbers to month names