Calculate DATE from Weekday and Week #

[Ray]

Hello - (XL2002 on XP)

I'm building a template to report daily sales and am looking for a way
to calculate the DATE for each day of the current week. Here's what I
have so far:
cell J3: =NOW() (formatted to MM/DD/YY)
K3: week # (calc'd using a really long formula, TOO long to write
here)
J9-J15: the days of the week
K9-K15: this is where I want the dates for the current week to
go

So, as a real example, right the values should be:
J3: 01/16/07
K3: 0703
J9: Sunday K9: 01/14/07
J10: Monday K10: 01/15/07
J11: Tuesday K11: 01/16/07
and so on....

What formulas would I put in K9:K15 to do this? OR, is there a better
way to get the same end result?

TIA,
Ray

 

[T. Valko]

Try this:

=J$3-WEEKDAY(J$3)+ROWS($1:1)

Copy down as needed.

Biff

 

[Sandy Mann]

I would suggest that you use =TADAY() in J3 in place of NOW() which has the
time included in it

For K9 try:

=J4-WEEKDAY(J4)+1

For K10 enter =K9+2 and copy down to K15


--
HTH

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

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

 

[T. Valko]

=TADAY()

That's literally how we pronounce it here in southwestern Pennsylvania!

creek = crick
you guys = youns or yunz
bologna = jumbo

Biff

 

[Sandy Mann]

LOL.

One job I could never do is that of a proof reader!

--

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

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

 

[Ray]

Thanks very much Sandy ... works perfectly!

Can you explain what the formula in K9 is actually doing? I mean, I
read the formula like this:
take today's DATE subtract today's WEEKDAY plus one ..... or, in
numbers,
1/17/07 - WEDNESDAY + 1

How does that formula equal Monday's date? In any event, it works --
and I thank you very much!

br//ray

 

[Sandy Mann]

Hi Ray,

If you check in Help for WEEKDAY() you will see that it returns the day
(number) of the week with Sunday = 1 and through to Saturday = 7. So if you
subtract the WEEKDAY() from ANY date then you will get the date of the
previous Saturday. You wanted the week to start on Sunday so we have to add
1 to that date to make it Sunday.

Actually the WEEKDAY() function is more correctly written as
=WEEKDAY(Date,1) because there are three options that can be used with an
argument of 1, 2 or 3. WEEKDAY(Date,1) is the default and XL will assume
the 1 if it is missed out.

WEEKDAY() with a 2 argument returns Monday as 1 through to Sunday as 7 and
with a 3 it returns Monday as zero and Sunday as 6

So from that it may look at first sight as if
=J3-WEEKDAY(J3,2)
would have been a better option to use because that, used with today's date,
(or as Biff would say taday's date <g> ), does return Last Sunday's date.
However, when it gets to NEXT Sunday, =J3-WEEKDAY(J3,2) will still be
returning the PREVIOUS Sunday not that day. That is why I chose to use the
first option and add one day

--
HTH

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

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

 

[David Biddulph]

Have you looked at Help to see what numbers the WEEKDAY() function returns?

 

[Ray]

Hello again -

Makes perfect sense ... thanks for the clear explanation!

rgds, ray