Help with Date formula

J

JR

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
 
C

CarlosAntenna

Put this formula in A3 and copy down the column.

=IF(WEEKDAY(A2)=2,A2-3,A2-2)
 
G

GaryE

JR:

Let's see if I understand what you want.
For clarity I will include the Day of the week in my dates.

A2 = Friday, March 24, 2006
A3 = Wednesday, March 22, 2006
A4 = Monday , March 20, 2006
A5 = Friday, March 17 2006

is that right? What do you want when today() = Tuesday, Thursday
Saturday, or Sunday? Or are you just going to use this on M,W,F. O
are you just using today() as an example?


Forgetting about the exceptions for a second if the data above i
correct the function below will do the trick.

place the following in A3
=IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=4,A2-2,IF(WEEKDAY(A2)=6,A2-2,"Don'
know what to do")))

The Weekday function returns the following
1 = Sunday
2 = Monday
3 = Tuesday etc.

Then just fill the series down to your hearts content.

Hope this Helps,
Gary
 
C

CarlosAntenna

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.
 
D

Dana DeLouis

With a valid date in A2, another option for A3 might be:

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

and copy down.
 
D

Dana DeLouis

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

Harlan Grove

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