Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    set of date (2000)

    in this workbook i have 2 sheets ANAGRAFE e RATE
    in the sheet ANAGRAFE cell D2 i have the value 60 in the cell J2 i have a date, for example 27/11/2004, i wolud want fill a set of date in the sheet RATE to start in the cell G2 for every 60 month day year, for example:

    sheet RATE
    in the cell G2 27/11/2004
    in the cell H2 27/12/2004
    in the cell I2 27/01/2005
    in the cell J2 27/02/2005
    ....
    in the cell BN2 27/10/2009

    this macro for every data present in the column D22000 and in the column J2:J2000 from ANAGRAFE

    when the actual date is the same in the cell G2 of sheet RATE delete the cell G2 and skip to left the other cell

    this in automaticly mode when i put the data in J2 e G2 of sheet ANAGRAFE

  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

    Re: set of date (2000)

    It does not require a macro (if I understand your request).

    In Sheet 'Rate' cell G2 enter the formula:
    <pre>=ANAGRAFE!J2</pre>


    Copy this from G2 to as many rows as you have.
    This will get the date from the appropriate column for each row.

    In Sheet 'Rate' cell H2 enter the formula:
    <pre>=DATE(YEAR(G2),MONTH(G2)+1,DAY(G2))</pre>


    This will add one month to the date.
    Copy the formula in H2 to H3:BN3

    Then copy H3:BN3
    down as many rows as desired.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    ok! tks. do you like to send my file with your mifdify in : gss.italy@iol.it
    or attache in this post
    i m a newbee

  4. #4
    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

    Re: set of date (2000)

    I gave the directions instead of attaching a modified file, since I think you will learn more by doing it, rather than having it done. Our goal is to give the "newbies" the knowledge to stop being "newbies". If we do all the work for you, you will not learn.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    OK! I have modify mi file with your formulas, Tks.

    but my new problem is when the actual day is the same in the cell G2, or in H2, or I2, ..... of sheet RATE, delete the cell and skip to the left the remaining cells
    Tks for your suggestion

  6. #6
    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

    Re: set of date (2000)

    I don't understand what you mean.

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: set of date (2000)

    <P ID="edit" class=small>(Edited by JohnBF on 23-Jan-04 12:02. )</P>I see that on the sheet called RATE you have a column called Anni. Do I guess correctly that column shows the number of years that the calculation is for? If so, are you saying that the calculation for Anni = 5 should show cells for 60 months, for Anni = 4 should be cells for 48 months, etc?

    If that is the case, change the formula in cell H2 to:

    =IF(COLUMNS($G2:H2)<($E2*12+1),DATE(YEAR(G2),MONTH (G2)+1,DAY(G2)),"")

    and copy it down column H and out to column BN.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    TKS! But i have many difficult to translate your function in italian.
    here why I would want my file modified.? Thanks
    for gently to re-attache my file with your modifcation.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    In Italian the formula should read;

    =SE(COLONNE($G2:H2)<($E2*12+1);DATA(ANNO(G2);MESE( G2)+1;GIORNO(G2));"")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    OK! IT WORK FINE!!!

    NEW PROBLEM:

    Delete cell in H2 when is the same date of today, and move to left the other cells of this lines and when this cell is deleted put in O2 of ANAGRAFE the date present in the last line of this new range.
    Tks, and sorry for my english

  11. #11
    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

    Re: set of date (2000)

    What you are asking will require a macro. We would need more info on all you would want to change with the macro and if you wanted it run automatically (for example when the file was open) or if you would just run it perhaps once a month.

    It might be possible with formulas, but I am still not clear on what you are trying to accomplish.

    Steve

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    Tks. Make me a macro...
    You are very gently to help me!

  13. #13
    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

    Re: set of date (2000)

    I can't help you until I understand what you are trying to do. You will have to give us some details of your goals and how it is setup.

    Steve

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: set of date (2000)

    Delete the cell in the range H2:H20 of sheet RATE when is the same date in D1 of sheet ANAGRAFE , and move to left the other cells of this lines, i have attached my file.

    Note: i make an experiment and i have delete H2 and move to left the remainnig cells but the other cells lose the refered formuals! Arg..................

  15. #15
    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

    Re: set of date (2000)

    I am still not sure what you want?
    You only want to do this to H2? or every row in col H of Rate. If every row, you could try this formula in H2:
    =IF(COLUMNS($G2:H2)<($E2*12+1),MAX(DATE(YEAR(G2),M ONTH(G2)+1,DAY(G2)),DATE(YEAR(ANAGRAFE!$D$1),MONTH (ANAGRAFE!$D$1)+1,DAY(G2))),"")

    It could be copied to H2:BN20

    Or you could just use instead (which has today explicit in the formula)
    =IF(COLUMNS($G2:H2)<($E2*12+1),MAX(DATE(YEAR(G2),M ONTH(G2)+1,DAY(G2)),DATE(YEAR(Today()),MONTH(Today ())+1,DAY(G2))),"")

    For a macro, I need to know
    You only want to do this to H2? or every row in col H of Rate?
    Do you need to keep the formulas in H2:H20 or are the values sufficient?
    Do you plan to run this manually or do you want it triggered in some way (perhaps checking when the workbook is open?)

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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