Round a date to previous Saturday

[davidlim]

hi guys,

i got a strange formula request for dates.

ok, i have bills and i'm tracking the due dates, which is always 30 days after bill date.

logically, each due date shall falls on any of the day, Mon to Sun.

to track payment, i'm looking at a way to know when is the previous Saturday before the due date.

these are the due dates (examples):

24-Jan-2014 (Fri) -- prev Sat = 18-Jan
24-Feb-2014 (Mon) -- prev Sat = 22-Feb
27-Mar-2014 (Thu) -- prev Sat = 22-Mar
24-Apr-2014 (Thu) -- prev Sat = 19-Apr
25-May-2014 (Sun) -- prev Sat = 24-May
24-Jun-2014 (Tue) -- prev Sat = 21-Jun
25-Jul-2014 (Fri) -- prev Sat = 19-Jul
24-Aug-2014 (Sun) -- prev Sat = 23-Aug

this way, my expected bill payment will not past the due dates and i try to pay bills in the weekends (Sat).

thanks!

 

[davidlim]

ok, seems i've managed to find the solution

=IF(WEEKDAY(A1,2)=6,A1,A1-MOD(A1,7))

 

[Somendra Misra]

Hi davidlim,

Another Solution:

=A1-CHOOSE(WEEKDAY(A1,2),2,3,4,5,6,0,1)

Regards,

 

[davidlim]

Hi davidlim,

Another Solution:

=A1-CHOOSE(WEEKDAY(A1,2),2,3,4,5,6,0,1)

Regards,

thanks!!!

 

[Haseeb A]

Hello David,

Here is one generic method to find Previous day. if a day falls on same day will give that day rather than previous. eg: If we look for Prev Sat & current date is Sat, then output will be same date.

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

0 - Sat, 1 - Sun, 2 - Mon .... 6 - Fri

 

[davidlim]

Hello David,

Here is one generic method to find Previous day. if a day falls on same day will give that day rather than previous. eg: If we look for Prev Sat & current date is Sat, then output will be same date.

=A1+1-WEEKDAY(A1+1-0)
0 - Sat, 1 - Sun, 2 - Mon .... 6 - Fri

that's another awesome yet simple solution too! much thanks!

just played around, the formula can be shortened to

=A1-WEEKDAY(A1-0)

 

[Haseeb A]

...the formula can be shortened to

=A1-WEEKDAY(A1-0)

...only if you want to look ALWAYS previous date,

eg: Today is 3/29/2014 (Sat), So with

=A1-WEEKDAY(A1-0) will give 3/22/2014 which is previous Saturday. With

=A1+1-WEEKDAY(A1+1-0) will keep 3/29/2014

 

[Kevin@Radstock]

Or another solution is using the FLOOR function.
=FLOOR(A1+7,7)-7

 

[davidlim]

...only if you want to look ALWAYS previous date,

eg: Today is 3/29/2014 (Sat), So with

=A1-WEEKDAY(A1-0) will give 3/22/2014 which is previous Saturday. With

=A1+1-WEEKDAY(A1+1-0) will keep 3/29/2014

ahh that make sense! thanks again!

 

[Haseeb A]

...the FLOOR function.
=FLOOR(A1+7,7)-7

Hello Kevin,

Only if you are using 1900 date system NOT 1904.