Calculate week end for week starting on saturday

[mrugesh]

Hi,

Can someone please help me out with a formula to calculate week end where saturday is the first day of week? For example, if date displayed in cell A1 is between 17th December till 23rd December, the week ending date displayed should be 23rd December.

Formula given below was perfect when sunday was first day of week. Now it does not work. I suspect the weekday function is to be blamed here...

=A1+-(WEEKDAY(A1)-1)+6

Regards,
Mrugesh.

 

[CWatts]

Try this:
=A1-MOD(A1,7)+6

 

[mrugesh]

Try this:
=A1-MOD(A1,7)+6

@ CWatts: Wow... This indeed worked. Now can you do me one more favour & explain the logic applied behind this formula?

 

[CWatts]

The difference is how it handles Saturday.

Excel considers Saturday the end of the week, so therefore when WEEKDAY is used, it gives it a number of 7, which would would indicate the end.

However, if we think of a week as a "container" that is filled by 7 days, by the time we hit Saturday excel considers the week "full" week, so when we use the MOD function to find the remainder, there is no remainder since the week is full.

Basically, all that is happening is that the starting point of the range is changing as the two formulas will return identical results for any day but Saturday.

Assuming A1 is December 10th:

=WEEKDAY(A1) is 7
=MOD(A1,7) is 0

We're basically changing the weekday count from 1-7 (Sun-Sat) to 0-6 (Sat - Fri). That one day offset is all that was needed.

 

[barry houdini]

Because the MOD version works on the dateserial number that version will only work if 1900 date system is being used - if you change to 1904 date system then that formula will return a Thursday. For that reason I would always use WEEKDAY function, i.e. for Friday week ending date try

=A1+7-WEEKDAY(A1+1)

That version can be adjusted for any day of the week, +2 is Thursday, +3 is Wednesday etc.

 

[mrugesh]

Because the MOD version works on the dateserial number that version will only work if 1900 date system is being used - if you change to 1904 date system then that formula will return a Thursday. For that reason I would always use WEEKDAY function, i.e. for Friday week ending date try

=A1+7-WEEKDAY(A1+1)

That version can be adjusted for any day of the week, +2 is Thursday, +3 is Wednesday etc.

@ Barry: Pure awesomeness!!! :D

You have just saved me a lot of grief. The data which I'm handling requires 1904 date system to be used in case of negative time values occur.