Return the Thursday Date before an Input Date Q

[John]

I am looking to return a date in Column B that is the Thursday before the
date in Column A. For example

A1 = 23/6/04
Thus B1 should = 17/6/04

It seems very easy, but I can't just use A1-4 because the date in A1 might
be anything (of 7 days) going forward

Thanks

 

[Norman Harker]

Hi John!

One way:
=A1-CHOOSE(WEEKDAY(A1,2),4,5,6,0,1,2,3)

It returns the same day if A1 is a Thursday

 

[Bob Phillips]

If perchance you want the previous Thursday if it is a Thursday, use

=A1-CHOOSE(WEEKDAY(A1,2),4,5,6,7,1,2,3)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Guys, I just knew it was possible, good spot with the Thursday before
Bob

 

[Soo Cheon Jheong]

John,

=A1-WEEKDAY(A1+2)


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Bob Phillips]

That returns Saturdays not Thursdays.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Daniel.M]

Bob,

That returns Saturdays not Thursdays.

???

You lucky guy : always having very long week-ends.

Perhaps you really had one too long, tough <bg>

Regards,

Daniel M.

 

[Harlan Grove]

One way:
=A1-CHOOSE(WEEKDAY(A1,2),4,5,6,0,1,2,3)

...

And another (inspired by Soo Cheon Jheong),

=A1-WEEKDAY(A1+4,3)

 

[Bob Phillips]

Don't know what I tried as I have scrubbed it now, but you are right.
Apologies to Soo Cheon Jheong.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sandy Mann]

...
..

And another (inspired by Soo Cheon Jheong),

=A1-WEEKDAY(A1+4,3)

and another inspired by both of you

=A1-MOD(A1+2,7)

Sandy

 

[Harlan Grove]

...
...

=A1-MOD(A1+2,7)

That's specific to the 1900 date system. It doesn't work in the 1904 date
system.

 

[Soo Cheon Jheong]

Bob,

That returns Thursdays not Saturdays for me (ExcelXP; Korean Version)


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^

 

[Bob Phillips]

Yes, it was my mistake, and I have already acknowledged it (see the thread).
No idea what I did, but I must have mis-tarnscribed it.

Bob