# Thread: Tricky IF statment (Excel 2000)

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

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

5. ## 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. ## 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. ## 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!")

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

#### Posting Permissions

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