Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2020
    Posts
    1

    Rounding to the closest Saturday

    So we have employees that can be hire on a Saturday, Sunday or Monday (01/04/2020, 01/05/2020, 01/06/2020, for example). I need a query forumla that will like at this Hire Date, add 21 days and then round down to the closet saturday regardless of whoch of those days they were fired on. So in this case, either one of those hire dates should reflect a date of 1/25/2020 for example. Any formula I've tried to do just adds 21 to the hire date.

    Any help is appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Posting expressions you tried would help to see what the variable/field names are, plus it would provide a starting point seeing as how this isn't quite simple. Also, 1 or 2 other examples would help to ensure the desired results. F'rinstance, if the date was 01/28/2020 should the result be 02/15/2020?
    EDIT - maybe it's simpler than I first imagined. Playing with date functions now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    January 6,7 or 8 results in 01/25/2020 (a Saturday) with this expression (obviously you have to alter the day value of date):
    Code:
    dateadd("d",21,#01/06/2020#) - weekday(dateadd("d",21,#01/06/2020#),1)
    It also assumes that day 1 in your system is Sunday. If not, change the "1" accordingly.
    Last edited by Micron; 01-17-2020 at 01:31 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-07-2018, 08:05 AM
  2. Replies: 2
    Last Post: 09-03-2015, 04:38 PM
  3. Replies: 5
    Last Post: 09-13-2013, 02:22 PM
  4. How to sort Day of week from Monday to Saturday
    By ultra5219 in forum Queries
    Replies: 3
    Last Post: 02-20-2013, 06:52 AM
  5. Count Sunday & Saturday between two days.
    By kashif.special2005 in forum Queries
    Replies: 2
    Last Post: 02-23-2012, 06:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums