Formula for Months in a Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brandon - 15 Mar 2007 20:05 GMT

I have 2 date fields. 1 field has a start date, the other has an end date.
I need a formula that will look at each date field separately and tell me
how many months are left in the year based on the year that the date field
shows.

Here's 2 quick examples to illustrate . . . .

Example 1: Date field one says 4/1/2006, Date field two says 3/31/2007. I
need Result one to show that 9 months were "used" in 2006 and Result two
would show that 3 months were "used" in 2007.

Example 2: Date field one says 1/1/2007, Date field two says 12/31/2007. I
need Result one to show that 0 months were "used" in 2006 and Result two
would show that 12 months were "used" in 2007.

Signature

Brandon
http://www.presentationsdirect.com

Reply to this Message

Sandy Mann - 15 Mar 2007 20:27 GMT

Brandon,

I don't understand what you mean by

> how many months are left in the year based on the year that the date field
> shows.

in Example 2 because both dates are in the same year.

For example 1 with the dates in A1 & B1

=DATEDIF(A1,DATE(YEAR(A1)+1,1,1),"m")
and
=DATEDIF(DATE(YEAR(B1)-1,12,31),B1,"m")

returns what you want but for example 2 they return 12 & 12

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>I have 2 date fields. 1 field has a start date, the other has an end date.
>I need a formula that will look at each date field separately and tell me
[quoted text clipped - 10 lines]
> I need Result one to show that 0 months were "used" in 2006 and Result two
> would show that 12 months were "used" in 2007.

Reply to this Message

Brandon - 15 Mar 2007 21:47 GMT

Thanks for the help. I know that in example 2 the dates are in the same
year. The idea is that we have contracts that sometimes start in the middle
of the year and end in the middle of the year. So we need a formula (based
on start date and end date) that tell us how many of those months are
assigned to 2006 and how many will be assigned to 2007 (for tax purposes).
So sometimes the start and end date will be in the same year, sometimes they
won't be. That's what makes the formula kind of tricky to get at.

Signature

Brandon

> Brandon,
>
[quoted text clipped - 25 lines]
>> I need Result one to show that 0 months were "used" in 2006 and Result
>> two would show that 12 months were "used" in 2007.

Reply to this Message

Sandy Mann - 15 Mar 2007 21:54 GMT

After further thought try:

=DATEDIF(A1,DATE(YEAR(A1)+(YEAR(B1)=YEAR(A1)+1),1,1),"m")

for the first date.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Thanks for the help. I know that in example 2 the dates are in the same
> year. The idea is that we have contracts that sometimes start in the
[quoted text clipped - 34 lines]
>>> I need Result one to show that 0 months were "used" in 2006 and Result
>>> two would show that 12 months were "used" in 2007.

Reply to this Message

Michael Bednarek - 16 Mar 2007 05:32 GMT

>I have 2 date fields. 1 field has a start date, the other has an end date.
>I need a formula that will look at each date field separately and tell me
[quoted text clipped - 10 lines]
>need Result one to show that 0 months were "used" in 2006 and Result two
>would show that 12 months were "used" in 2007.

I may be missing something, but don't these trivial formulae do what you
want (A1 = Start Date, B1 = End Date):

Months in the Start Date's year:
=13-MONTH(A1)

Months in the End Date's year:
=IF(YEAR(B1)<>YEAR(A1),MONTH(B1),0)

Signature

Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Reply to this Message

Sandy Mann - 16 Mar 2007 11:12 GMT

> I may be missing something, but don't these trivial formulae do what you
> want (A1 = Start Date, B1 = End Date):
[quoted text clipped - 4 lines]
> Months in the End Date's year:
> =IF(YEAR(B1)<>YEAR(A1),MONTH(B1),0)

"But he's got no clothes on!" <g>

Doh! Yes of course, well done.

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

Reply to this Message

Sandy Mann - 16 Mar 2007 11:32 GMT

Actually no, not quite. It is OK with the examples the OP gave but it
depends on what the OP is looking for. If the start date ia 1/1/2007 then
the first formula will return 12 regardless of what the End date is. So
1/1/2007 and 30/11/2007 returns 12 & 0

But then my formula doesn't work either mmmmm.........

More thought required

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>> I may be missing something, but don't these trivial formulae do what you
>> want (A1 = Start Date, B1 = End Date):
[quoted text clipped - 8 lines]
>
> Doh! Yes of course, well done.

Reply to this Message

Roger Govier - 16 Mar 2007 11:44 GMT

Hi Sandy

Isn't it just a question of amending the first formula

=IF(YEAR(A1)=YEAR(B1),MONTH(B1)-MONTH(A1),13-MONTH(A1))

Signature

Regards

Roger Govier

> Actually no, not quite. It is OK with the examples the OP gave but it
> depends on what the OP is looking for. If the start date ia 1/1/2007
[quoted text clipped - 18 lines]
>>
>> Doh! Yes of course, well done.

Reply to this Message

Sandy Mann - 16 Mar 2007 12:22 GMT

Hi Roger,

No this is still not quite right. For the OP's 2nd example of Start:
1/1/2007 End 12/31/2007 he is asking for 0 & 12 whereas even with the
amendment it returns 11 & 0. Adding 1 to the amendment of first formula
would correct that but it would still be the "wrong way round" If that is
not importantant then that may be the better option otherwise:

=DATEDIF(A1,MAX(A1,DATE(YEAR(B1),1,1)),"m")
for the first formula and:
=DATEDIF(MAX(A1,DATE(YEAR(B1),1,1)),B1,"m")+1
for the second may do what the OP wants.

It looks like the OP's dates are always from the 1st day of the start month
to the end day of the end month. If that is not the case then there may be
problems because of how DATEDIF() workd out what a month is.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Hi Sandy
>
[quoted text clipped - 24 lines]
>>>
>>> Doh! Yes of course, well done.

Reply to this Message

Michael Bednarek - 16 Mar 2007 12:04 GMT

>Actually no, not quite. It is OK with the examples the OP gave but it
>depends on what the OP is looking for. If the start date ia 1/1/2007 then
[quoted text clipped - 4 lines]
>
>More thought required

Easily corrected. First formula:
=IF(YEAR(A1)<>YEAR(B1),13-MONTH(A1),MONTH(B1)-MONTH(A1)+1)

It is not clear from the OP's post what should happen if the period
spans more than two years. The total number of months is trivial:
=DATEDIF(A1,B1,"m")+1

Signature

Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Reply to this Message

Sandy Mann - 16 Mar 2007 12:44 GMT

Hi Michael,

Yes as I said to Rager, it is not clear either if the OP's dates are always
for the the 1st of the month to the last day of the month.

I assume that you know that when I said:

>>More thought required

I was talking to myself.

Signature

Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>>Actually no, not quite. It is OK with the examples the OP gave but it
>>depends on what the OP is looking for. If the start date ia 1/1/2007 then
[quoted text clipped - 11 lines]
> spans more than two years. The total number of months is trivial:
> =DATEDIF(A1,B1,"m")+1

Reply to this Message