# Thread: Days in a month

1. looking for something simple to drive the date of a particular month.

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

Steve

3. Slightly simpler:

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

If you have installed the Analysis ToolPak add-in or if you have Excel 2007:

=DAY(EOMONTH(A1,0))

This will not work for users with Excel 2003 or before who do not have the Analysis ToolPak installed.

4. [quote name='HansV' post='784317' date='13-Jul-2009 11:11']Slightly simpler:

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

If you have installed the Analysis ToolPak add-in or if you have Excel 2007:

=DAY(EOMONTH(A1,0))

This will not work for users with Excel 2003 or before who do not have the Analysis ToolPak installed.[/quote]
Thanks Steve/Hans. It is much concised with Analysis Toolpak.

5. [quote name='prasad' post='784323' date='13-Jul-2009 12:27']Thanks Steve/Hans. It is much concised with Analysis Toolpak.[/quote]
What abt this one? Need total days of FY upto month starting from April.

6. You can simplify the formula to

=DATE(2009,A2+4,1)-DATE(2009,4,1)

7. [quote name='HansV' post='784471' date='14-Jul-2009 10:21']You can simplify the formula to

=DATE(2009,A2+4,1)-DATE(2009,4,1)[/quote]
a blank cell = ?
is it equivalent to text?
if I link the year value to a blank cell, it still make calculations.

8. Depending on the context, Excel will interpret a blank cell as a 0 or as an empty string.

If you want to check for a blank cell:

=IF(A2="","",DATE(2009,A2+4,1)-DATE(2009,4,1))

If A2 is blank, the formula will return an empty string, otherwise it will return a number of days.

9. [quote name='HansV' post='784473' date='14-Jul-2009 10:56']Depending on the context, Excel will interpret a blank cell as a 0 or as an empty string.

If you want to check for a blank cell:

=IF(A2="","",DATE(2009,A2+4,1)-DATE(2009,4,1))

If A2 is blank, the formula will return an empty string, otherwise it will return a number of days.[/quote]
" blank cell as a 0 "
got it.
"as an empty string."
trying to understand.

Again, how will it calculte tha days in case of leapyear.

10. If you replace 2009 with a leap year in the formula, Excel will automatically count February as 29 days. No other adjustments are needed.

If you want the formula to automatically use the current fiscal year instead of the fixed year 2009, you can use

=IF(A2="","",DATE(YEAR(TODAY())-(MONTH(TODAY())<4),A2+4,1)-DATE(YEAR(TODAY())-(MONTH(TODAY())<4),4,1))

The formula will use 2009 as I write this, but next July, it will use 2010.

11. [quote name='HansV' post='784475' date='14-Jul-2009 11:08']If you replace 2009 with a leap year in the formula, Excel will automatically count February as 29 days. No other adjustments are needed.

If you want the formula to automatically use the current fiscal year instead of the fixed year 2009, you can use

=IF(A2="","",DATE(YEAR(TODAY())-(MONTH(TODAY())<4),A2+4,1)-DATE(YEAR(TODAY())-(MONTH(TODAY())<4),4,1))

The formula will use 2009 as I write this, but next July, it will use 2010.[/quote]
Got it absolutely. Thank u very much Hans.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•