How to calculate last date of a week from any date assuming that week starts on monday

[Hrishi]

Hello, I want to calculate the last date of a week from any date with presumption that week starts from monday and ends on sunday. I have a formula
=IF(WEEKDAY(date)=7,date+1,date+(7-WEEKDAY(date)),,, BUT this does not work on date 1-1-17 and 31-12-18 etc may be it because this day is either monday or sunday.

Can anyone help me Plz????.

 

[steve the fish]

Try one of these. They are slightly different in how they handle the last day of the week itself:

=A1-WEEKDAY(A1)+8
=A1-((WEEKDAY(A1)<>1)*(WEEKDAY(A1)-8))

 

[Hrishi]

Ya It worked very well, thanks ,,,Thank you so much....

 

[barry houdini]

=A1-((WEEKDAY(A1)<>1)*(WEEKDAY(A1)-8))

You can simplify this version as follows:

=A1-WEEKDAY(A1-1)+7