Excel – Complex Cell Formula

datemicrosoft excel

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.

Best Answer

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))
Related Question