Date Functions

[Sean.rogers]

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.)

 

[Ivyleaf]

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.

 

[Ron Rosenfeld]

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

 

[Sean.rogers]

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

 

[David Biddulph]

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).

 

[Ron Rosenfeld]

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

=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