Complex Cell Formula

I want a formula that will return a date that depends on the date in another cell, but modified by the day of the week of the date in that cell.

If cell A1 has a date value that is a Sunday, Monday, or Tuesday, I want cell A2 to return the date of the Friday before (e.g. if A1 is Monday 16 Oct 2017, A2 would return Friday 13 Oct 2017).
If the date in cell A1 is Wednesday through Saturday then the date in cell A2 would be two days prior to the day (e.g. If A1 is Thursday 19 Oct 2017 then cell A2 would return a date of Tuesday 17 Oct 2017).

I want the formula to do all of the above calculations in one formula.

BamaBoy

Posted 2017-10-16T21:40:41.587

Reputation: 19

Answers

It seems that the date you want is two days earlier than the date in cell A1, except that if the result is on a weekend, it should be the Friday before the weekend.

You can use the WEEKDAY function to return a number representing the day of the week a date is (1=Sunday, 7=Saturday).

A simple way to get the date you want is

=IFS(WEEKDAY(A1-2)=1,A1-4,WEEKDAY(A1-2)=7,A1-3,TRUE,A1-2)

If your version of Excel doesn't have the IFS function, you can use nested IF functions:

=IF(WEEKDAY(A1-2)=1,A1-4,IF(WEEKDAY(A1-2)=7,A1-3,A1-2))

Blackwood

Posted 2017-10-16T21:40:41.587

Reputation: 3 058

I tried the formula with the IFS (IF give me an error saying "You've entered too many arguments for this function", but it returns "#NAME?" Any suggestions? – BamaBoy – 2017-10-16T22:45:43.667

I stand corrected...it worked! You are a GENIUS! The 2nd formula worked! Thank you so much! I have been scratching my head for over a week trying to make something similar work. Thank you again for taking the time to respond to my question! – BamaBoy – 2017-10-16T22:55:25.580

Ala @BamaBoy - Since you say it works, please plan to accept the answer that works by checking it to give it acceptance and to close the loop on your inquiry. – Pimp Juice IT – 2017-10-18T00:14:14.380

Consider:

=CHOOSE(WEEKDAY(A1),A1-2,A1-3,A1-4,A1-2,A1-2,A2-2,A1-2)

Because:

Gary's Student

Posted 2017-10-16T21:40:41.587

Reputation: 15 540