I want a particular cell to know the upcoming Saturday date.

[Guest]

I want to put the future Saturday date in a cell. In other words, any time
that I open my spreadsheet, regardless of whether it is on a Tuesday or
Thursday or any day, I want that particular cell to know what the upcoming
Saturday date is.

 

[Bob Phillips]

=TODAY()+CHOOSE(WEEKDAY(TODAY()),6,5,4,3,2,1,0)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

You are a guru! That is perfect! Thank you very much!
Mae

 

[Sandy Mann]

=TODAY()+7-WEEKDAY(TODAY()+7)

or if you want Saturday to show the following Saturday not that day:

=TODAY()+8-WEEKDAY(TODAY()+8)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Myrna Larson]

Hi, Sandy:

Do you have a typo in one of your formulas? For me, both return the same date.

Why WEEKDAY(TODAY()+7)? That's the same as WEEKDAY(TODAY()), no?

Can't you use just

=TODAY()-WEEKDAY(TODAY())+7

The subtraction takes you to the Saturday BEFORE today, adding 7 takes you to
the Saturday AFTER today. This gives the same result as your formulas.

 

[Sandy Mann]

Do you have a typo in one of your formulas? For me, both return the same
date

Yes they do for today or any day that is not a Saturday but, unlesss I have
got it wrong, the +8 formula will return the *next* Saturday's date not that
day's date.

I tested it by referencing a column of dates with both formulas instead of
hard coding TODAY() and both formulas returned the same date until the
originating cell was a Saturday when there was a 7 day difference.

Or am I wrong?
--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Oh I see!

Why WEEKDAY(TODAY()+7)? That's the same as WEEKDAY(TODAY()), no?

Yes you are correct there. I think that I was all caught up in the second
formula returning the *next* Saturday that I failed to read your post
properly.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk