Calculating the Number of Days in a Month

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

In excel, is there a way to calculate the total number of days in a
particular month using a formula. For example, if I have 4/1/05, is
there any formula I can use to tell me that the month of April has 30
days in it?
 
I'm having problems getting that to work, it's not returning the number
of days for the month. Also in this situatiuon I'm looking for a
formula in which i can put in the first day of any month and get the
total number of days (regardless of weather there are 30 or 31 days in
the month). Thanks!!
 
Also, if you have the Analysis Toolpack installed you could use:

=DAY(EOMONTH(A1,0))

Note: The huge disadvantage (in my view) of the Analysis Toolpack functions
is that they won't work on machines where it is not installed nor in Excel
in other languages .

Regards,
KL


KL said:
Try putting the formula in [b1] en enter any date into [a1].

Regards,
KL

I'm having problems getting that to work, it's not returning the number
of days for the month. Also in this situatiuon I'm looking for a
formula in which i can put in the first day of any month and get the
total number of days (regardless of weather there are 30 or 31 days in
the month). Thanks!!
Click to expand...
Click to expand...
 
Still having problems, i entered the formula in b1, date in a1 and the
retuned data was 1/0/1900. Any ideas where I'm messing this up??

5/1/2004
=30+DAY(A1)-DAY(A1+30)

1/0/1900
 
Sorry, my mistake.

change the formula to:

=31+DAY(A1)-DAY(A1+31)

Select [B1]
Go to menu Format>Cell
On the 1st tab (Number) in the Category box choose General
Press Enter
Voila!

KL
 
Also...

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

OR

=DAY(EOMONTH(A1,0))

The latter requires that you enable the Analysis ToolPak...

Tools > Add-Ins > and check Analysis ToolPak

Hope this helps!
 
Sorry, my mistake.

change the formula to:

=31+DAY(A1)-DAY(A1+31)

Select [B1]
Go to menu Format>Cell
On the 1st tab (Number) in the Category box choose General
Press Enter
Voila!

this way I guess it is neater than =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Regards,
KL
 
Forget it - doesn't work a 100% either. Sorry.

KL


KL said:
Sorry, my mistake.

change the formula to:

=31+DAY(A1)-DAY(A1+31)

Select [B1]
Go to menu Format>Cell
On the 1st tab (Number) in the Category box choose General
Press Enter
Voila!

this way I guess it is neater than =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Regards,
KL


Harlan Grove said:
KL wrote...
...

Why not use =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) ?
Click to expand...
Click to expand...
 
Hi,

I am back :-D
After having checked day-by-day, month-by-month this one seems to work:

=MIN(31+DAY(A1)-DAY(A1+31),31)

Regards,
KL

KL said:
Forget it - doesn't work a 100% either. Sorry.

KL


KL said:
Sorry, my mistake.

change the formula to:

=31+DAY(A1)-DAY(A1+31)

Select [B1]
Go to menu Format>Cell
On the 1st tab (Number) in the Category box choose General
Press Enter
Voila!

this way I guess it is neater than =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Regards,
KL


Harlan Grove said:
KL wrote...
Also, if you have the Analysis Toolpack installed you could use:

=DAY(EOMONTH(A1,0))
...

Why not use =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) ?
Click to expand...
Click to expand...
Click to expand...
 
Yep, I forgot about that. It's definitely more desirable. :) Thanks
for bringing it to my attention! Much appreciated!
 
In excel, is there a way to calculate the total number of days in a
particular month using a formula. For example, if I have 4/1/05, is
there any formula I can use to tell me that the month of April has 30
days in it?
Click to expand...

With your date in A1:

=32-DAY(A1-DAY(A1)+32)

And format the result as General or Number


--ron
 
Sorry, my mistake.

change the formula to:

=31+DAY(A1)-DAY(A1+31)

Select [B1]
Go to menu Format>Cell
On the 1st tab (Number) in the Category box choose General
Press Enter
Voila!
Click to expand...

And if you enter 30 Jan 2005 you discover there are 59 days in January ????

Better using:

=32-DAY(A1-DAY(A1)+32)


--ron
 
Back
Top