Results 1 to 4 of 4

Thread: date range

  1. #1

    date range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to create this:

    1. type a date into a cell that is the first or last date of a specific month...or perhaps the name of the month (any will do)
    2. excel then automatically fills a defined range in a column with all the dates of that month filled in..no more...no less
    3. the difficulty is that the months have different numbers of days in each, and excel has to intuit that info

    I've tried every trick I know...I can't get it done. can you help?

    Bill

  2. #2
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Here's the start of one solution:

    If your date is in cell A1, the formula in B1 would be:
    =DATE(YEAR(A1),MONTH(A1),DAY(A1)-DAY(A1)+1)

    The formula in B2 (and copied down to B31) would be:
    =IF(MONTH(B1+1)=MONTH(B1),B1+1,"")

    Works well for all but Feb : )

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Using candybg's formula in B1,
    make the formula in B2

    =IF(MONTH($B$1+ROW()-1)=MONTH($B$1),B1+1,"")

    and drag down to B31

  4. #4
    Works perfectly! Thank you so much! You two are excel wizards!

Posting Permissions

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