How to calculate days in the month

shanmer

New Member
Joined
Mar 10, 2002
Messages
2
Hi,

I would like to find a function that can calculate the number of days in the month for a given date that is provided in the following format dd/mm/yy.

For example 23/02/02, would result in 28.
23/02/04, would result in 29.
31/01/01, would result in 31.
etc....

Thanks in Advance,
Sharon
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
On 2002-03-11 21:03, shanmer wrote:
Hi,

I would like to find a function that can calculate the number of days in the month for a given date that is provided in the following format dd/mm/yy.

For example 23/02/02, would result in 28.
23/02/04, would result in 29.
31/01/01, would result in 31.
etc....

Thanks in Advance,
Sharon

Sharon,

One way:

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

where A2 house a date like the examples you provided.

Aladin
 
Upvote 0
Looks like Alladin beat me to it just
as I was about to post :)

Just to add to that format the cell as
general....

Ivan
 
Upvote 0
On 2002-03-11 21:03, shanmer wrote:
Hi,

I would like to find a function that can calculate the number of days in the month for a given date that is provided in the following format dd/mm/yy.

For example 23/02/02, would result in 28.
23/02/04, would result in 29.
31/01/01, would result in 31.
etc....

Thanks in Advance,
Sharon

If you have the Analysis ToolPpak add-in installed (Tools-Add-ins, Analysis ToolPak if you don't), you can use the following formula:

=EDATE(A1,1)-A1

This assumes your date is in A1.

Hope this helps,

Russell
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top