Help with Date formula

[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

[CarlosAntenna]

Put this formula in A3 and copy down the column.

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

--
Carlos

"JR" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[JR]

perfect

"CarlosAntenna" wrote:

> Put this formula in A3 and copy down the column.
>
> =IF(WEEKDAY(A2)=2,A2-3,A2-2)
>
> --
> Carlos
>
> "JR" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

[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.
--
Carlos

"JR" <[email protected]> wrote in message
news:[email protected]...
> perfect
>
> "CarlosAntenna" wrote:
>
> > Put this formula in A3 and copy down the column.
> >
> > =IF(WEEKDAY(A2)=2,A2-3,A2-2)
> >
> > --
> > Carlos
> >
> > "JR" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

[Dana DeLouis]

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

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

and copy down.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"JR" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Just another option with any date in A1, then copied down.
=A1-MOD(86349937, 6*WEEKDAY(A1) - 1)

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"CarlosAntenna" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Carlos
>
> "JR" <[email protected]> wrote in message
> news:[email protected]...
>> perfect
>>
>> "CarlosAntenna" wrote:
>>
>> > Put this formula in A3 and copy down the column.
>> >
>> > =IF(WEEKDAY(A2)=2,A2-3,A2-2)
>> >
>> > --
>> > Carlos
>> >
>> > "JR" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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