1

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.

Improve this question
0

2 Answers 2

Reset to default
1

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))
Improve this answer
3
  • 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
    Oct 16, 2017 at 22:45
  • 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
    Oct 16, 2017 at 22:55
  • 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.
    – Vomit IT - Chunky Mess Style
    Oct 18, 2017 at 0:14
1

Consider:

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

Because:

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .