week ending funciton

[Dylan @ UAFC]

I am needing a formula that will returna week ending date
based, this is primary way the data base needs to be sorted.

Or week ends on SAT

 

[Glenn]

I am needing a formula that will returna week ending date
based, this is primary way the data base needs to be sorted.

Or week ends on SAT

=A1-WEEKDAY(A1)+7

 

[Mike H]

Hi,

If I understand correctly you want the next saturday from a date and if the
date is saturday and you want the folowing saturday change the middle 7 to 14

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

Mike

 

[Rick Rothstein]

This... IF(WEEKDAY(A1)=7,7,7)... will always return 7... why not just use 7
and save the function calls?

a

 

[Mike H]

Rick,

I wasn't sure what the OP wanted to do if the original date was a saturday
which is why I posted this because WEEKDAY(A1)=7,14,7)... returns the next
saturday. I pointed this out to the OP in my post

Mike

 

[Ron Rosenfeld]

Hi,

If I understand correctly you want the next saturday from a date and if the
date is saturday and you want the folowing saturday change the middle 7 to 14

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

Mike

More efficient might be:


Saturday does not advance
=A1+7-WEEKDAY(A1)

Saturday does advance
=A1+8-WEEKDAY(A1-6)

Only one function call instead of three.
--ron