+ Reply to Thread
Results 1 to 9 of 9

How to round a date to one of two different weekdays

  1. #1
    Registered User
    Join Date
    03-30-2021
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    4

    Question How to round a date to one of two different weekdays

    I have a list with delivery times from a supplier where i have fixed shipment days (Tuesday and Friday) every week.

    in my data set i often have all kinds of dates (different week days)

    does anyone know a formula where i can round down a certain date to either the closest Tuesday or Friday.

    e.g:
    - delivery date Wednesday Thursday need to be rounded down to Tuesday (same week)
    - Delivery date Monday needs to be rounded down to Friday (the week before)
    - If the delivery date is already a Tuesday or a Friday i just need to keep that date.

    any help would be highly appreciated.
    Last edited by MaxPoulsen; 04-08-2021 at 04:59 AM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    13,511

    Re: How to round a date to one of two different weekdays

    With the date in B2 how about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,700

    Re: How to round a date to one of two different weekdays

    In F2 then copy down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    03-30-2021
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to round a date to one of two different weekdays

    Thanks Fluff13 that seems to work. but this actually raises an extra question.

    i have a list of bankholidays and i would like to avoid that the formula rounds down to a bank holiday. it should round down to either the tuesday or the friday before the holiday.

    e.g.

    - Monday 5. April would round down to Friday 2. April.
    But 2. April is "Easter Friday" and therefore in this case I would like the formula to round down to Tuesday 30. March instead.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,922

    Re: How to round a date to one of two different weekdays

    WORKDAY.INTL has a parameter for holiday list: have you tried adding that?

    =WORKDAY.INTL(B2+1,-1,"1011011",holidays) where "Holidays" is a list (named range?) of holiday dates.
    Last edited by JohnTopley; 04-07-2021 at 02:36 AM.

  6. #6
    Registered User
    Join Date
    03-30-2021
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to round a date to one of two different weekdays

    JohnTopley: thankyou for your input. it seems to have solved my problem.

    im trying to figure out the logic of the Workday.intl formula and i get most of it. but im struggeling to figure out why i need to add +1 to the date and the subtract -1 in the next step. B2+1,-1.

    can you help me explaining this

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,721

    Re: How to round a date to one of two different weekdays

    Quote Originally Posted by MaxPoulsen View Post
    but im struggeling to figure out why i need to add +1 to the date and the subtract -1 in the next step. B2+1,-1
    Try place together to see the difference:
    (1) =WORKDAY.INTL(B2,-1,"1011011",$R$1:$R$4)
    and
    (2) =WORKDAY.INTL(B2+1,-1,"1011011",$R$1:$R$4)

    If B2 fell into tue or fri or holidays, i.e, B2="8-Jan-2021" (Fri)
    (1) came back to prv tue 5-Jan
    (2) stay in same day (Fri).
    +1 then -1, to avoid this.

  8. #8
    Registered User
    Join Date
    03-30-2021
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to round a date to one of two different weekdays

    arr i see, thanks for clarifying.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,652

    Re: How to round a date to one of two different weekdays

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ 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. NETWORKDAYS.INTL issue when start date on weekdays end date on weekend
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2017, 11:15 PM
  2. Check to see if a date is 2 WEEKDAYS apart
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2014, 04:37 PM
  3. calculate end date using start date and number of specific weekdays
    By freekystyley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-12-2008, 01:14 PM
  4. Would like to add 20 working/weekdays to a date
    By Pete in forum Excel General
    Replies: 8
    Last Post: 10-25-2005, 01:05 PM
  5. Date, Weekdays
    By Heckstein in forum Excel General
    Replies: 3
    Last Post: 09-11-2005, 06:05 PM
  6. Subtracting weekdays from a date:
    By Richard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2005, 10:06 PM
  7. count weekdays in a date range
    By benb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2005, 11:06 AM

Tags for this Thread

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