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

[imastarr]

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!

 

[Joe4]

Welcome to the Board!

Try this:

=IF(OR(WEEKDAY(H3)=6,WEEKDAY(H3)=7,WEEKDAY(H3)=1),H3-3,H3)

 

[imastarr]

Welcome to the Board!

Try this:

=IF(OR(WEEKDAY(H3)=6,WEEKDAY(H3)=7,WEEKDAY(H3)=1),H3-3,H3)

Perfect!

 

[Joe4]

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

 

[imastarr]

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

 

[imastarr]

Welcome to the Board!

Try this:

=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?

 

[Joe4]

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