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

See here for some ideas: http://www.ozgrid.com/Excel/day-of-week.htm

Dave
--
Brevity is the soul of wit.


"Ray" wrote:

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

Try this:

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

Copy down as needed.

Biff

"Ray" wrote in message
oups.com...
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

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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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

=TADAY()

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

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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

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


with @tiscali.co.uk


"T. Valko" wrote in message
...
=TADAY()

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

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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

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 wrote:
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


with @tiscali.co.uk


"T. Valko" wrote in message
...
=TADAY()

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

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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

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


with @tiscali.co.uk


"Ray" wrote in message
ups.com...
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 wrote:
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


with @tiscali.co.uk


"T. Valko" wrote in message
...
=TADAY()

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

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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

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

"Ray" wrote in message
ups.com...
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!

Hello again -

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

rgds, ray


Sandy Mann wrote:
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


with @tiscali.co.uk


"Ray" wrote in message
ups.com...
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 wrote:
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


with @tiscali.co.uk


"T. Valko" wrote in message
...
=TADAY()

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

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

Biff

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Ray" wrote in message
oups.com...
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