+ Reply to Thread
Results 1 to 19 of 19

Find previous Saturday and next friday dates given a date

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #2
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    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
    Ash

  3. #3
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

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

  4. #4
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

    In Example 3 the dates are wrong ?

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #7
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

    Can you please test this...........


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


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

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #9
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

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

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


    Please Login or Register  to view this content.


    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. #11
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    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. #12
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

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

  13. #13
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

    The Saturday = Saturday thing is not working out

  14. #14
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

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

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

  15. #15
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    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. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #17
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

    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. #18
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,580

    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. #19
    Forum Contributor
    Join Date
    08-23-2016
    Location
    India
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    194

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

    Great !!! Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dates in Excel - If Saturday or Sunday, return Friday's date
    By gjrr4x1 in forum Excel General
    Replies: 6
    Last Post: 02-12-2015, 02:56 PM
  2. move date to next Monday if date is on a Friday, Saturday or Sunday
    By ea223 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2014, 01:54 PM
  3. Replies: 4
    Last Post: 01-06-2014, 09:48 PM
  4. Delete Rows with dates older than previous Friday
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2013, 04:51 PM
  5. Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  6. Change Weekend Date To The Previous Friday
    By MarkMcCann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2012, 09:45 AM
  7. date of last friday of previous month
    By tkaplan in forum Excel General
    Replies: 7
    Last Post: 11-14-2005, 02:10 PM

Bookmarks

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