number of days in a month

  • Thread starter Thread starter Boenerge
  • Start date Start date
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
 
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.
 
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
 
One way. You can also use eomonth if analysis toolpak

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))
 
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...
 
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
 
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
 
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)
 
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
 
Back
Top