Results 1 to 9 of 9
Thread: Tricky IF statment (Excel 2000)

20040511, 18:31 #1
 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<=A14,"Late",if(C1=A13,"late tomorrow")
Heres the twist: A1 will always say todays date at 9AMFormated 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!

20040511, 18:52 #2
 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

20040511, 19:04 #3
 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]

20040511, 19:26 #4
 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

20040512, 15:43 #5
 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?

20040512, 15:48 #6
 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? (MonFri) 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....

20040512, 16:11 #7
 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 12May04 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

20040512, 16:13 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20040512, 19:03 #9
 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