Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tricky IF statment (Excel 2000)

    Hello,
    I am trying to create an IF statement to tell me if an order on a excel sheet is 4 days or older than a date pasted in A1 make it say "Late" and if the cell is only 3 days old it will say late tomorrow. In other words:
    IF(C1<=A1-4,"Late",if(C1=A1-3,"late tomorrow")
    Heres the twist: A1 will always say todays date at 9AM-Formated as mm/dd/yy hh:mm AM So it needs to take into account the hours in a day as well.
    What I'm trying to do is this: I need a formula to tell me if the date is column C is either 6 business days old, 4 business days old or 3 business days older then the date and time in A1.
    Like I said...this is a tough one so if you are up for a challenge, go for it! I have attached a sheet that is much more helpful then my confussing text. Any help would be great!

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

    Re: Tricky IF statment (Excel 2000)

    Try:

    <pre>=IF(INT(A1)-INT(C1)>=4,"Late",IF(INT(A1)-INT(C1)>=3,"Late Tomorrow")
    </pre>


    Or

    <pre>=IF(TODAY()-INT(C1)>=4,"Late",IF(TODAY()-INT(C1)>=3,"Late Tomorrow")
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IF statment (Excel 2000)

    No Dice [img]/forums/images/smilies/sad.gif[/img] No matter what the date said, it never would say anything other than LATE [img]/forums/images/smilies/sad.gif[/img]

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

    Re: Tricky IF statment (Excel 2000)

    If you are copying the first formula down a column, you may need to make the reference to cell A1 absolute like this:

    <pre>=IF(INT($A$1)-INT(C1)>=4,"Late",IF(INT($A$1)-INT(C1)>=3,"Late Tomorrow","")
    </pre>


    The above works in my test workbook. If it does not work for you, then you probably will need to post a sample workbook that shows the problem you are having.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IF statment (Excel 2000)

    It worked!! Awesome! You saved alot of hair pulling! Questio: what does INT in the formula stand for?

  6. #6
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tricky IF statment (Excel 2000)

    One more thing... Do you know how to make a formula recognize only work days? (Mon-Fri) Meaning, I want to make a formula say the day of the week rather then the date in number form. So if today is 5/12/04 I want another box to read the date and display Wednesday
    Is that possible? Cause if it is, I would then make an if says If(A2=Monday, B2,IF(A2=Tuesday,B2,If.....and so on....

  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: Tricky IF statment (Excel 2000)

    <P ID="edit" class=small>(Edited by JohnBF on 12-May-04 10:11. )</P>INT returns the Integer (whole number) portion of a value.

    You might want to look at loading the Analysis Toolpak and using =NETWORKDAYS(start,finish,holiday_list), which will ignore weekends and ignore a given list of holidays. You can also use MOD(ULUS) 7 on the date, which returns the following:
    <table border=1><td align=left>Saturday</td><td align=left> 0</td><td align=left>Sunday</td><td align=left> 1</td><td align=left>Monday</td><td align=left> 2</td><td align=left>Tuesday</td><td align=left> 3</td><td align=left>Wednesday</td><td align=left> 4</td><td align=left>Thursday</td><td align=left> 5</td><td align=left>Friday</td><td align=left> 6</td></table>
    So to operate only on weekdays, you can use something like:

    =IF(MOD(A2,7)>1,TRUE,FALSE)

    Or possibly

    =IF(MOD(A2,7)>1,CHOOSE(MOD(A2,7)-1,"Mon","Tues","Wed","Thurs","Fri"),"Weekend - party time!")
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Tricky IF statment (Excel 2000)

    Excel also has a WEEKDAY function. With a date in cell A2, the formula =WEEKDAY(A1) will return a number corresponding to the day of the week, with Sunday = 1, ..., Saturday = 7.

    If you want to display the day of the week, you can change the number format of the cell(s) containing a date (Format | Cells..., Number tab)

    The INT function rounds a number down to the next lower whole number, for example INT(7.8) = 7

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

    Re: Tricky IF statment (Excel 2000)

    In addition to what John and Hans have said, to get a cell that contains a date to display the day name you can go to Format/Cell and create a custom format of "ddd" to display Mon, Tue, etc. or "dddd" to display Monday, Tuesday, etc.
    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
  •