# Find previous Saturday and next friday dates given a date

1. ## Find previous Saturday and next friday dates given a date

Hi,

I have a situation where i have a work week from Satuday to Friday.

I have a date...........say UserDate.

Example1 of what i want:
UserDate = 5/2/16
SatDate = 4/30/16
FriDate = 5/6/16

Example2 of what i want:
UserDate = 4/30/16
SatDate = 4/30/16
FriDate = 5/6/16

Example3 of what i want:
UserDate = 5/14/16
SatDate = 5/9/16
FriDate = 5/15/16

How to calculate the SatDate and FriDate?

2. ## Re: Find previous Saturday and next friday dates given a date

Use this for Last Saturday
=A2-WEEKDAY(A2,2)-1

Use this for Next Friday
=A2-WEEKDAY(A2,2)+5

Where A2 has UserDate

3. ## Re: Find previous Saturday and next friday dates given a date

Wait.......... That will not work

4. ## Re: Find previous Saturday and next friday dates given a date

In Example 3 the dates are wrong ?

5. ## Re: Find previous Saturday and next friday dates given a date

I am soooooo sorry........the calendar apparently switched on me..........

lets try this again:

Example3 of what i want:
UserDate = 5/12/16
SatDate = 5/7/16
FriDate = 5/13/16

6. ## Re: Find previous Saturday and next friday dates given a date

thanks! i think your soln does work. again, so sorry about the mistake on the date. thanks!

7. ## Re: Find previous Saturday and next friday dates given a date

Use this for Last Saturday
=A2-WEEKDAY(A2,16)+1

Use this for Next Friday
=A2-WEEKDAY(A2,16)+7

8. ## Re: Find previous Saturday and next friday dates given a date

hi i tried the formula you suggested in my vba code and when i try to run it the "16" is causing it to stop. It does not like the 16.

also, the formula does not appear to work at the "ends" of the week. for example, recall that my week runs Saturday to Friday.

If i pick a day say saturday 5/7/16 then i would want the date returned to be 5/7/16 since this is on a saturday........but the formula returns 4/30/16

any ideas?

9. ## Re: Find previous Saturday and next friday dates given a date

I just checked it and it is working OK at my side..... check the attached file...............and I am not very sure about VBA codes so sorry can't help you there.

10. ## Re: Find previous Saturday and next friday dates given a date

ok i got the 16 to work.......i used this

This formula does actually work. my only concern now is that the formula will only work if
the person has excel 2010. I am not sure if this will be true in all cases...........

is there a way to do this without using functions only applicable to 2010?

11. ## Re: Find previous Saturday and next friday dates given a date

This should work in all versions from 2000 onward

=A2-WEEKDAY(A2) -------SATURDAY

=A3-WEEKDAY(A3)+6 --------- FIRDAY

12. ## Re: Find previous Saturday and next friday dates given a date

or maybe not..........

13. ## Re: Find previous Saturday and next friday dates given a date

The Saturday = Saturday thing is not working out

14. ## Re: Find previous Saturday and next friday dates given a date

Try this..........

For Saturday.........=IF(WEEKDAY(A1)=7,A1,A1-WEEKDAY(A1))

15. ## Re: Find previous Saturday and next friday dates given a date

What if the selected date is FIRDAY.............what should be the friday date for it ? next friday or the same friday ?

16. ## Re: Find previous Saturday and next friday dates given a date

Hi,

So there are two dates to determine: Start-date and End-date.

If user date is 5/6/16 = A friday.........and this is in the start section.......then the adjusted start date is the earliest Saturday
prior to 5/6/16 which is 4/30/16.

Similarly, if user date is 5/6/16= a friday.......and this is in the end section.......then the adjusted date is the latest/closest friday
to the given date which is the day itself.......so end date is 5/6/16

does this make sense?

17. ## Re: Find previous Saturday and next friday dates given a date

I hope this will work

For Saturday...........=IF(WEEKDAY(A1)=7,A1,A1-WEEKDAY(A1))

For Friday..............=IF(WEEKDAY(A1)=6,A1,A1-WEEKDAY(A1)+6)

18. ## Re: Find previous Saturday and next friday dates given a date

nice.......i think this works......i will continue checking to be sure and let you know but i dont know why but i was looking for some
elegant math formula.......i like your approach......just use an if statement to figure out the edges....thanks.

19. ## Re: Find previous Saturday and next friday dates given a date

Great !!! Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1