number of days in a month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Boenerge - 08 Dec 2007 20:17 GMT

Hi
Is it possible to create a formula that would automayically insert the
number of days in the current or previous month. At present I am having to
manually insert the number into the formula, an axample below, month is where
I want the number inserting:
=SUM(D61*37.5)/7.5*month

Reply to this Message

T. Valko - 08 Dec 2007 20:27 GMT

For the current month:

=DAY(EOMONTH(NOW(),0))

For the previous month:

=DAY(EOMONTH(NOW(),-1))

These require the Analysis ToolPak add-in be installed.

Signature

Biff
Microsoft Excel MVP

> Hi
> Is it possible to create a formula that would automayically insert the
[quoted text clipped - 4 lines]
> I want the number inserting:
> =SUM(D61*37.5)/7.5*month

Reply to this Message

Boenerge - 08 Dec 2007 20:40 GMT

Thanks
Jason

> For the current month:
>
[quoted text clipped - 14 lines]
> > I want the number inserting:
> > =SUM(D61*37.5)/7.5*month

Reply to this Message

T. Valko - 08 Dec 2007 21:32 GMT

You're welcome!

Signature

Biff
Microsoft Excel MVP

> Thanks
> Jason
[quoted text clipped - 18 lines]
>> > I want the number inserting:
>> > =SUM(D61*37.5)/7.5*month

Reply to this Message

Pete_UK - 08 Dec 2007 20:36 GMT

For the current month (i.e. based on TODAY()), you could try this:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) -
DATE(YEAR(TODAY()),MONTH(TODAY()),0)

For the previous month, try this:

=DATE(YEAR(TODAY()),MONTH(TODAY()),0) -
DATE(YEAR(TODAY()),MONTH(TODAY())-1,0)

This gives the number of days in the month, so multiply your formula
by this.

Hope this helps.

Pete

On Dec 8, 8:17 pm, Boenerge <Boene...@discussions.microsoft.com>
wrote:

> Hi
> Is it possible to create a formula that would automayically insert the
> number of days in the current or previous month. At present I am having to
> manually insert the number into the formula, an axample below, month is where
> I want the number inserting:
> =SUM(D61*37.5)/7.5*month

Reply to this Message

Don Guillett - 08 Dec 2007 20:38 GMT

One way. You can also use eomonth if analysis toolpak

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi
> Is it possible to create a formula that would automayically insert the
[quoted text clipped - 4 lines]
> I want the number inserting:
> =SUM(D61*37.5)/7.5*month

Reply to this Message

Bill Kuunders - 08 Dec 2007 20:43 GMT

create a table with months numbers and number of days.

0 31 for Dec being the previous for a Jan date
1 31 for Jan
2 28 for Feb
3 31
etc

=VLOOKUP(MONTH(TODAY()),A1:B13,2)

for previous month
=VLOOKUP(MONTH(TODAY())-1,A1:B13,2)

Bill K
Greetings from New Zealand

> Hi
> Is it possible to create a formula that would automayically insert the
[quoted text clipped - 4 lines]
> I want the number inserting:
> =SUM(D61*37.5)/7.5*month

Reply to this Message

Rick Rothstein (MVP - VB) - 09 Dec 2007 04:40 GMT

> create a table with months numbers and number of days.
>
[quoted text clipped - 8 lines]
> for previous month
> =VLOOKUP(MONTH(TODAY())-1,A1:B13,2)

Leap years?

Rick

Reply to this Message

Stan Brown - 09 Dec 2007 13:45 GMT

Sat, 8 Dec 2007 12:17:02 -0800 from Boenerge
<Boenerge@discussions.microsoft.com>:

> Is it possible to create a formula that would automayically insert the
> number of days in the current or previous month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))-DATE(YEAR(TODAY
()),MONTH(TODAY()),DAY(TODAY()))

If Analysis Toolpak is loaded, you can use the much simpler
=EOMONTH(TODAY(),0)-EOMONTH(TODAY(),-1)

For previous month, the corresponding formulas are

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-DATE(YEAR(TODAY
()),MONTH(TODAY())-1,DAY(TODAY()))

=EOMONTH(TODAY(),-1)-EOMONTH(TODAY(),-2)

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Reply to this Message

Stan Brown - 09 Dec 2007 13:48 GMT

T. Valko and Don Guillett's solutions are better than mine. Instead
of subtracting last days of two consecutive months, they use the day
number of the last day of the month concerned.

Thanks guys!

Sun, 9 Dec 2007 08:45:36 -0500 from Stan Brown
<the_stan_brown@fastmail.fm>:

> Sat, 8 Dec 2007 12:17:02 -0800 from Boenerge
> <Boenerge@discussions.microsoft.com>:
[quoted text clipped - 13 lines]
>
> =EOMONTH(TODAY(),-1)-EOMONTH(TODAY(),-2)

Signature

Reply to this Message

Rate this thread: