# IF Statement with dates

#### montanaaggie

##### Board Regular
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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

##### Board Regular
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:

Replies
8
Views
657
Replies
7
Views
451
Replies
3
Views
598
Replies
3
Views
468
Replies
5
Views
491

1,171,686
Messages
5,876,893
Members
433,217
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back