How do I get Excel to return a date Rounded back 2 Mondays based on the date
I give it. Example. If I give it todays date which is 4/16/08 I need it to
return 4/7/08. The trick is I need it to return that 4/7/08 date regardless
if I input 4/14/08-4/18/08. (I need it to round back 2 Mondays.)

On Apr 16, 10:14*pm, Sean.rogers
wrote:
How do I get Excel to return a date Rounded back 2 Mondays based on the date
I give it. Example. If I give it todays date which is 4/16/08 I need it to
return 4/7/08. The trick is I need it to return that 4/7/08 date regardless
if I input 4/14/08-4/18/08. (I need it to round back 2 Mondays.)

Hi Sean,

Try this maybe:

=D3-WEEKDAY(D3,3)-7

Assuming your date is in cell D3.

Cheers,
Ivan.

On Wed, 16 Apr 2008 05:14:01 -0700, Sean.rogers
wrote:

How do I get Excel to return a date Rounded back 2 Mondays based on the date
I give it. Example. If I give it todays date which is 4/16/08 I need it to
return 4/7/08. The trick is I need it to return that 4/7/08 date regardless
if I input 4/14/08-4/18/08. (I need it to round back 2 Mondays.)


=A1-6-WEEKDAY(A1-1)

You didn't specify Sat/Sun, but they, too, will go back 2 Mondays -- In other
words, 4/14/08-4/20/08 will return 4/7/08

--ron

Thank you. It work beautifully. One more question. I am self tought in Excel
so can you please explane the Logic in this function?

"Ron Rosenfeld" wrote:

On Wed, 16 Apr 2008 05:14:01 -0700, Sean.rogers
wrote:

How do I get Excel to return a date Rounded back 2 Mondays based on the date
I give it. Example. If I give it todays date which is 4/16/08 I need it to
return 4/7/08. The trick is I need it to return that 4/7/08 date regardless
if I input 4/14/08-4/18/08. (I need it to round back 2 Mondays.)


=A1-6-WEEKDAY(A1-1)

You didn't specify Sat/Sun, but they, too, will go back 2 Mondays -- In other
words, 4/14/08-4/20/08 will return 4/7/08

--ron

If you type the word WEEKDAY into Excel help, it'll tell you the syntax of
the WEEKDAY function, and give examples. The same applies for any other
Excel function which is confusing you (except for DATEDIF).
--
David Biddulph

"Sean.rogers" wrote in message
...
Thank you. It work beautifully. One more question. I am self tought in
Excel
so can you please explane the Logic in this function?

"Ron Rosenfeld" wrote:

On Wed, 16 Apr 2008 05:14:01 -0700, Sean.rogers
wrote:

How do I get Excel to return a date Rounded back 2 Mondays based on the
date
I give it. Example. If I give it todays date which is 4/16/08 I need it
to
return 4/7/08. The trick is I need it to return that 4/7/08 date
regardless
if I input 4/14/08-4/18/08. (I need it to round back 2 Mondays.)


=A1-6-WEEKDAY(A1-1)

You didn't specify Sat/Sun, but they, too, will go back 2 Mondays -- In
other
words, 4/14/08-4/20/08 will return 4/7/08

--ron

On Wed, 16 Apr 2008 06:54:00 -0700, Sean.rogers
wrote:

Thank you. It work beautifully. One more question. I am self tought in Excel
so can you please explane the Logic in this function?

=A1-6-WEEKDAY(A1-1)

The basic idea is that, with a date in A1, the formula

A1-WEEKDAY(A1) will return the previous Saturday.

Actually, A1-WEEKDAY(A1-DOW) returns the previous Saturday, where DOW = Day Of
Week and Sunday = 1; Saturday = 7. But, of course, WEEKDAY(A1) =
WEEKDAY(A1-7), so both are the same.

DOW becomes important when you want to return a different day than Saturday.

So, to return the Monday of the week befo

=A1-6-WEEKDAY(A1-6-DOW) --
A1-6-WEEKDAY(A1-6-2) --
A1-6-WEEKDAY(A1-8)

Since the weekdays are 1-7 --
A1-6-WEEKDAY(A1-MOD(8,7)) --
A1-6-WEEKDAY(A1-1)

--ron