number of days in a month

B

Boenerge

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
 
T

T. Valko

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

Pete_UK

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
 
D

Don Guillett

One way. You can also use eomonth if analysis toolpak

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

Boenerge

Thanks
Jason

T. Valko said:
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.
Click to expand...
 
B

Bill Kuunders

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
 
R

Rick Rothstein \(MVP - VB\)

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

Leap years?

Rick
 
S

Stan Brown

Sat, 8 Dec 2007 12:17:02 -0800 from Boenerge
Is it possible to create a formula that would automayically insert the
number of days in the current or previous month.
Click to expand...

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

Stan Brown

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
 
Top