Formula for Months in a Year

B

Brandon

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

Sandy Mann

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.
Click to expand...
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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
B

Brandon

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.

--
Brandon


Sandy Mann said:
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.
Click to expand...
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

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
Click to expand...
 
S

Sandy Mann

After further thought try:

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

for the first date.

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Brandon said:
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.
Click to expand...
 
M

Michael Bednarek

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.
Click to expand...

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)
 
S

Sandy Mann

Michael Bednarek said:
microsoft.public.excel:
Click to expand...
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)
Click to expand...

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

Doh! Yes of course, well done.

--

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
S

Sandy Mann

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

--

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
R

Roger Govier

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))
 
M

Michael Bednarek

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
Click to expand...

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
 
S

Sandy Mann

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.

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
S

Sandy Mann

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:
I was talking to myself.

--
Regards,

Sandy

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Totals by Year 3
Conditional formula's in excel-calculating with dates / age andaffiliation date calculus 3
formula 1
Dynamic border formatting based on dates. Use VBA? 1
formula to count matching days between two date ranges (for largelist of names) 2
Help with formula 3
Max Value in Year 3
Calc Number of Months PER YEAR Between Dates 2

Top