Complex Cell Formula

date microsoft-excel

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

Consider:

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

Because:

enter image description here

Related

How to access database server on my desktop from my laptop?
How uninstall Win 10 update? (it broke my miracast)
Partitioning a natural number $n$ in order to get the maximum product sequence of its addends
Fundamental group of complement of $n$ lines through the origin in $\mathbb{R}^3$
Is there a non-cyclic group with every subgroup characteristic?
Are there simple algebraic operations for continued fractions?
proof of $\sum\nolimits_{i = 1}^{n } {\prod\nolimits_{\substack{j = 1\\j \ne i}}^{n } {\frac{{x_i }}{{x_i - x_j }}} } = 1$ [duplicate]
Find $x$ from $3^x\cdot x^3 = 1$
How to find the vertices angle after rotation
Is $\mathbf{R}^\omega$ in the uniform topology connected?
What is the difference between analytic combinatorics and the theory of combinatorial species?
How to prove there are no more positive integers that are products of 2 and 3 consecutive numbers?