Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If / Then Formula (Excel 97)

    I am trying to write a formula that will automatically adds on 45 days if the date falls into a certain time of month (i.e. January 1st to February 1st).
    Example - Cell AM1=01/26/06
    I want Cell AL1= -45 days instead of -38 days

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

    Re: If / Then Formula (Excel 97)

    I am not sure I understand your question. I think this is what you are asking for:

    <pre>=IF(AND(AM1>=DATE(2006,1,1),AM1<DATE(2006,2,1 )),AM1-45,AM1-38)
    </pre>

    Legare Coleman

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: If / Then Formula (Excel 97)

    Hi Lania,

    Another formula you could use is:
    =AM1-38-AND(AM1>DATE(YEAR(AM1),1,0),AM1<DATE(YEAR(AM1),2,2 ))*7

    This is perhaps more flexible than Legare's, since it effectively disregards the year portion of the date in AM1.

    Another option, if you want to test against the current year and allow for the year to change, is:
    =AM1-38-AND(AM1>DATE(YEAR(TODAY()),1,0),AM1<DATE(YEAR(TODA Y()),2,2))*7

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (Excel 97)

    Your good................ anybody ever tell you, "You should do this for a living".
    Thanks for the help........ I think this will work fine for what I am doing!

  5. #5
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (Excel 97)

    How would I circumvent the formula if I only wanted to make -45 days from 12/26 to 02/02

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: If / Then Formula (Excel 97)

    Hi Lania,

    In that case, you could use something like:
    =AM1-38-AND(AM1>DATE(YEAR(AM1)-1,12,25),AM1<DATE(YEAR(AM1),2,2))*7

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (Excel 97)

    Hey Thanks............... next time your in town (Atlanta GA) I'll buy you the first round!

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

    Re: If / Then Formula (Excel 97)

    I did do it for a living for 40 years before I retired. Glad I could help.
    Legare Coleman

Posting Permissions

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