Results 1 to 11 of 11

Thread: Days in a month

  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    looking for something simple to drive the date of a particular month.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Prasad

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Attached Files Attached Files
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can simplify the formula to

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

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Prasad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Prasad

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #11
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Prasad

Posting Permissions

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