Formula to determine date of the Monday preceding a date

M

Mike

I need a formula to determine the date of the Monday that precedes a date.

Any help would be appreciated.

Thanks,

Mike
 
B

Biff

Hi Mike!

Try this with the date entered in A1:

=IF(A1-WEEKDAY(A1,3)=A1,A1-WEEKDAY(A1,3)-7,A1-WEEKDAY
(A1,3))

Format the cell as Date

Biff
 
J

JE McGimpsey

Note: The formula below will return the same date on Mondays. If the
*previous* Monday is desired:

=A1-1-WEEKDAY(A1-1,3)
 
M

Mike

This formula assumes the date is in C5, and will return the preceding Moday.

=IF(WEEKDAY($C$5)=2,$C$5-0,IF(WEEKDAY($C$5)=3,$C$5-1,IF(WEEKDAY($C$5)=4,$C$5
-2,IF(WEEKDAY($C$5)=5,$C$5-3,IF(WEEKDAY($C$5)=6,$C$5-4,IF(WEEKDAY($C$5)=7,$C
$5-5,IF(WEEKDAY($C$5)=0,$C$5-6,0)))))))
 
Top