Help with Date formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning Excel Masters,

I would like to ask for help.

I have a report where I have to show only Monday, Wednesday and Friday
dates. I need to go backwards starting with today’s date. So for example in
A2 I will put =today().

However in A3 I need the formula to show the date for the previous Wednesday
(3/22). Now writing this is generally easy, however when Monday (3/27) comes
around I will need A3 to show the date for Friday (3/24), and then A4 will
need to show Wednesday’s date (3/22) etc…

Your help is appreciated.

JR
 
Of course that only works if the date in A2 is a M, W, or F.

If A2 can be any date, you would need something like this:
=IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=1,A2-2,IF(MOD(WEEKDAY(A2),2)<>0,A2-1,A
2-2)))

You may need to consider this because the today() function is volatile.
 
With a valid date in A2, another option for A3 might be:

=A2-MOD(128,WEEKDAY(A2)+3)

and copy down.
 
If A2 can be any date, ...
=IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=1,A2-2,IF(MOD(WEEKDAY(A2),2)<>0,A2-1,A
2-2)))
Click to expand...

Just another option with any date in A1, then copied down.
=A1-MOD(86349937, 6*WEEKDAY(A1) - 1)
 
Dana DeLouis wrote...
Just another option with any date in A1, then copied down.
=A1-MOD(86349937, 6*WEEKDAY(A1) - 1)
Click to expand...
....

Or if one wishes to understand what's going on,

=A2-CHOOSE(WEEKDAY(A2),2,3,1,2,1,2,1)
 
Back
Top