# Compare date, if Sat, Sun or Mon, subtract 3

#### imastarr

##### New Member
I have a date in a spreadsheet in cell H3. If that date falls on a Saturday, Sunday or Monday, I would like excel to return a date in I3 that reflects three days earlier. So if H3 is a Monday, I need it to show Friday. If it is a Sunday, it needs to return a Thursday and if it is a Saturday, it needs to return a Wednesday. It can ignore Tuesday, Wednesday and Thursday and Fridays.

I hope I've explained this well enough! I am a novice when it comes to Excel and rely on these forums for assistance (which is always great!)

Thanks!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Welcome to the Board!

Try this:
Code:
``=IF(OR(WEEKDAY(H3)=6,WEEKDAY(H3)=7,WEEKDAY(H3)=1),H3-3,H3)``

Welcome to the Board!

Try this:
Code:
``=IF(OR(WEEKDAY(H3)=6,WEEKDAY(H3)=7,WEEKDAY(H3)=1),H3-3,H3)``

Perfect!

Glad it worked out for you!

The key is the WEEKDAY function. Here is a good write-up on it, if you are curious as to how it works: WEEKDAY function - Excel

Thanks. I will give it a try myself using this next time or if I need to change something in this sheet.

Welcome to the Board!

Try this:
Code:
``=IF(OR(WEEKDAY(H3)=6,WEEKDAY(H3)=7,WEEKDAY(H3)=1),H3-3,H3)``

I've tried this myself with little success, but, what if I needed to have have the remaining days report the day before and if a day falls on a Holiday, report the day before the holiday?

You will want to incorporate the WORKDAY function into your equation.
See: WORKDAY - Excel

Replies
4
Views
345
Replies
8
Views
233
Replies
2
Views
248
Replies
7
Views
413
Replies
6
Views
170

1,207,260
Messages
6,077,351
Members
446,279
Latest member
hoangquan2310

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