IF Statement with dates

[montanaaggie]

I am trying to complete the following
Column F = established date (10/01/200). Right now I have it formatted so that upon entering that in, it'll display "Sunday, October 01, 2006" I want the column next to it (G), to be that day + 10 days. Currrently it displays "Wednesday, October 11, 2006" Is there a way should that + 10 (G column date) day be a Saturday or Sunday that it jumps to the following Monday.

I can insert a hidden column between the two. What I tried with the following example
F column date 10/05/2006, so displays "Thursday, October 05, 2006
G date calculates to be 10/15/2006 "Sunday, October 15, 2006)
H date needs to be Monday and I inserted the equation "=IF(G8="Sunday*", "G8+1", G8)", and It still displays "Sunday, October 15, 2006

Anyone have any ideas?

Thanks,
Dave

 

[Joe4]

Let's say that your date is in cell F8, then use this formula to get the result you want:

=F8+10+IF(WEEKDAY(F8+10,2)>5,8-WEEKDAY(F8+10,2),0)

 

[PolarBear]

This also works:

=IF(WEEKDAY(F8+10)=1,F8+11,IF(WEEKDAY(F8+10)=7,F8+12,F8+10))

It uses the Weekday function which returns a number from 1 (Sunday) to 7 (Saturday). The first test looks to see if it's a Sunday, in which case it
adds 11 to F8 to get Monday, while the second checks to see if it's a Saturday, in which case it adds 12 to F8. If it's not a Saturday or Sunday, it just adds 10 to F8.

Hope this helps,

Kevin

 

[Joe4]

PolarBear,

Our concepts are both pretty much the same (using the WEEKDAY function). There are many different way to do it using this.

One trick you can use to get rid of the nested IF is by using of the second argument of the WEEKDAY function. If you do not use (as in your case), Sunday=1 and Saturday=7. If you use it and set it equal to 2 (like I did), then Saturday=6 and Sunday=7.

So then instead of having to check if Weekday=1 or Weekday=7, you can simply check for Weekday>5.

(Ironically, I always did it the other way too, until earlier this week when someone clued me in to the optional second argument for WEEKDAY -- you can find more details on it in Excel's help). :wink: