Calculate Days in a Month

L

LGG

Hi. I need help finding a formula to calculate the number of days in a month
depending on the month you choose from a drop down menu.
Example:

In cell A1 user will be ask to select a month from a drop down menu.
In cell B1 I want the number of days in the month corresponding to the month
selected in A1. So, if February is chosen on A1, then 28 (days) would appear
on B1.

I was trying to do if statements, but I keep getting an error. Is there an
easy way to do this?

Thanks
LGG
 
B

Biff

Hi!
if February is chosen on A1, then 28 (days) would appear
Click to expand...

What about leap years when Feb has 29 days?

One way:

In some location list the months:

J1 = January
J2 = February
J3 = March
...
J12 = December

=DAY(DATE(YEAR(TODAY()),MATCH(A1,J1:J12,0)+1,0))

Biff
 
P

paulrm906

Hello LGG

Well the easist way that I know to count the days in a month
=DAY(EOMONTH($A$1;0)) but as for the Leap Year I am not 100% sure.

I hope this is of help to you.


Paul Maynard
 
A

Arvi Laanemets

Hi

Add a sheet Months.
Months!A1="Month"
Months!A2=01.01.2006
(or any other 1st of month in any valid date format)
Months!A3=01.02.2006
Select the range Months!A2:A3, and format in some valid date format like
"mmmm yyyy" or "yyyy.mmmm".
Copy the range down for some reasonable amount of rows - you get a list of
months to be selected.

Select the list of months, and define it as a named range Months. Hide sheet
Months.

On your entry sheet, apply to some cell (p.e. B1) a data validation list
with Source=Months, and format the cell like month list on sheet Months.
Into another cell, enter the formula:
=IF($B$="","",DAY(DATE(YEAR($B$1),MONTH($B$1)+1,0)))
,or, with Analysis Toolpack Ad-In activated
=IF($B$="","",DAY(EOMONTH($B$1,0)))
 
R

Ron Rosenfeld

Hi. I need help finding a formula to calculate the number of days in a month
depending on the month you choose from a drop down menu.
Example:

In cell A1 user will be ask to select a month from a drop down menu.
In cell B1 I want the number of days in the month corresponding to the month
selected in A1. So, if February is chosen on A1, then 28 (days) would appear
on B1.

I was trying to do if statements, but I keep getting an error. Is there an
easy way to do this?

Thanks
LGG
Click to expand...

To check the days of the month, I assumed that

1. The month you are choosing is from a list of text strings, and not real
Excel dates formatted to show just the month.

2. The year is "this" year, so February will only show 29 days if "this" year
is a leap year.

B1:

=DAY((A1&YEAR(TODAY()))+32-DAY((A1&YEAR(TODAY()))+32))


--ron
 
T

TomHinkle

Easy.... One line....
ASSUMING the value in A1 is a date


in B1:
=Date(year(a1),month(a1)+1,1))- Date(year(a1),month(a1),1))
 
Top