Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Date Manipulation ? (Excel 2002/2003)

    Hi

    I have a problem with manipulating dates.

    If anyone can help I would be most grateful. Please see attached

    Many thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation ? (Excel 2002/2003)

    You want a formula to return the 1st of February on or after the date specified in D10. Is that correct? If so, use
    <code>
    =DATE(YEAR(D10)+(D10>DATE(YEAR(D10),2,1)),2,1)
    </code>
    Otherwise, please provide a more clear explanation or more examples.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    re> You want a formula to return the 1st of February on or after the date specified in D10. Is that correct? If so, use

    =DATE(YEAR(D10)+(D10>DATE(YEAR(D10),2,1)),2,1)

    This not quite what I am looking for


    I will try to give a better example

    If the date entered = Jan 2006 then the example below would read Prices From 01-Nov-06 to 01-Feb-08, when the new prices come into force.
    ie jan 06 to Jan 07 12 months plus Jan 07 1 month 13 months total

    If the date entered was March 2006 it would only need to last 12 months because it would run through Feb 2007 when the prices increased


    Prices From 01-Nov-06 To 01-Nov-07 this only calculates for 12 months

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation ? (Excel 2002/2003)

    I'm afraid I don't understand.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    HI Hans

    Please see attached.

    What I am trying to do here is, as we have price increases every Feb, If a customer signs a contract in Jan we couldn't increase prices after one month it would have to be the following Feb ie 13 months

    or if he signed up in November we couldn't increase prices after 3 months. There has to be minimum of 12 months between signing and price increases.

    But if he singed in March 12 months hence would include the next Feb price increase.

    Sorry to be so tiresome.

    Best Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    Hans,
    I understand what Braddy is after, but I cannot get the function working....I know I am using Date incorrectly, but let the same formula just give you a starting point...

    =IF(AND(MONTH(D10)>=2,MONTH(D10)<=10),DATE(D10,D10 +12,D10),IF(MONTH(D10)=11,DATE(D10,D10+15,D10),IF( MONTH(D10)=12,DATE(D10,D10+14,D10),DATE(D10,D10+13 ,D10))))
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation ? (Excel 2002/2003)

    Does this do what you want?
    <code>
    =DATE(YEAR(D10),MONTH(D10)+12+(MONTH(D10)=1)+3*(MO NTH(D10)=11)+2*(MONTH(D10)=12),1)</code>

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    That looks like it does it...

    Nice formula...if you get change would you care to explain it...
    Tx
    Regards,
    Rudi

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    Hi Hans

    If you were British you would surely get a knighthood, or maybe an honourary one <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Works just fine, like Rudi I'm not sure how it works but it surely does.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation ? (Excel 2002/2003)

    > if you get change

    How much spare change do you have? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    =DATE(YEAR(D10),MONTH(D10)+12+(MONTH(D10)=1)+3*(MO NTH(D10)=11)+2*(MONTH(D10)=12),1)

    The DATE function has arguments year, month, day.
    For year we take the year of the date entered in D10, and since Braddy wants the 1st of the month, we use 1 as day.
    For the month, we start by adding 12 to the month of the date in D10. We could also have added 1 to the year, that has the same effect, but I wanted to stay close to Braddy's instructions.
    The further additions to month use the fact that TRUE = 1 and FALSE = 0 in Excel. So, for example, MONTH(D10)=11 evaluates to 1 for a date in November, and to 0 for other months.
    Hence, 3*(MONTH(D10)=11) evaluates to 3 for November, and to 0 for other months. The total addition for a date in November is 12+3 = 15, as requested. Similar for the other 'special' months December and January.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    Hi Hans

    Just a rider to this how do I adjust the formula to show nothing if D10 is blank?

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Manipulation ? (Excel 2002/2003)

    <code>=IF(D10="","",previous formula without the =)</code>

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    Hi Hans

    Thanks again.
    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Manipulation ? (Excel 2002/2003)

    How much spare change...... uhm...about <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>....
    I notice you dished out the explanation for free though....very kind. (However, you will not get far if you made a business of it!!!) - <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Tx for the clear and concise breakdown...it makes good cents <img src=/S/grin.gif border=0 alt=grin width=15 height=15> now....
    Cheers
    Regards,
    Rudi

Posting Permissions

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