Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto date series (97+)

    I need a spreadsheet which displays the days (m/d/yy) of current month starting in cell A3 ending at cell A34?/A33? (depends on # of days in month). I need the data to appear automatically without any user intervention every time a user opens the spreadsheet. Another option might be that the user run a macro after opening the spreadsheet.
    Tried to do it myself using function(now) and then strings but then I have trouble with number of days for different months---there must be a more simple solution.
    TIA
    Smbs

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    Smbs

    There is an EOMONTH function in the Analysis Toolpack but most people don't use it if the book is shared and other toolpack functions are not used.

    Its easy enough with basic functions <img src=/S/compute.gif border=0 alt=compute width=40 height=20>. Put =Now() in a cell - say A1. Then the first day of that month is

    =Date(Year(A1),Month(A1),1)

    The first day of next month (including December/January) is

    =Date(Year(A1),Month(A1)+1,1)

    and the last day of the current month is

    =Date(Year(A1),Month(A1)+1,0)

    (Yes, it works ) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    So - days in the month are First day of next month minus first day of this month

    =Date(Year(A1),Month(A1)+1,1) - Date(Year(A1),Month(A1),1)

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    Thanx for quick reply--I am now trying to use the formulae that u gave me for amount of days etc but I am getting syntax errors when placing code in vba editor ---"Date(Year(A1),Month(A1)+1,1) - Date(Year(A1),Month(A1),1)" and "Date(Year(A1),Month(A1),1)"-----missing ")"
    any ideas????
    Thanx
    Smbs

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    Put this formula in A3:

    <pre>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
    </pre>


    Then put this formula in A4 and fill it down through A33:

    <pre>=IF(ISNUMBER(A3),IF(MONTH(A3+1)=MONTH(A3),A3+ 1,""),"")
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    Thanx -works like a charm---if u have the patience maybe u would explain formula #2
    thanx again
    Smbs

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    I'll try.

    Here is the formula for reference:

    <pre>=IF(ISNUMBER(A3),IF(MONTH(A3+1)=MONTH(A3),A3+ 1,""),"")
    </pre>


    The ISNUMBER(A3) checks to see if the cell above contains a number to avoid a #Value error for the cells past the last day of the month in months that have less than 30 days. If it does not contain a number, then the IF returns a null string ("") to the cell. If it does contain a number, then the second IF checks to see if the date in the previous cell and that date plus 1 day are in the same month. If they are, it returns the previous cell's date plus 1 day. If it does not, then it returns a null string.
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto date series (97+)

    Thanx ---really appreciate it I learn all the time!!
    Smbs

Posting Permissions

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