+ Reply to Thread
Results 1 to 8 of 8

Date pattern help: Tuesday, Wednesday, Friday, Saturday

  1. #1
    Registered User
    Join Date
    10-05-2020
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Date pattern help: Tuesday, Wednesday, Friday, Saturday

    Is there a way to do a series fill according to this date pattern:
    Saturday, Tuesday, Wednesday, Friday
    I'm interested in the date format only 10/5/20

    Example
    For the whole month of October
    Saturday 10/3/20
    Tuesday 10/6/20
    Wednesday 10/7/20
    Friday 10/9/20
    ...

    Thanks in advance

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    if A1 stored 10/3 then below would be one option

    A2: =WORKDAY.INTL(A1,1,"1001001")
    copied down
    (the 1s in the above binary sequence denote the week days to skip, mon thru sun)

  3. #3
    Registered User
    Join Date
    10-05-2020
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    Thanks so very much XLent. That is excellent.

    I don't think it is possible but I'm asking anyways. Can the formula specify to keep the exact format as what was entered in cell A1. The cell in A1 is in date format 10/5/20, Arial 14 font, and with a border. The formula strips the format, font, and the border.

    Also, when I sort the dates to newest to oldest I get REF!.

    Thanks again.
    Last edited by falcios; 10-05-2020 at 05:06 AM.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    formatting is independent so, copy the format from your original cell and paste (format) over the remainder.

    re: sorting - the formula is designed to look at the cell above so if you wish to store in the opposite order you would need to either
    a) modify the "prior" cell (look below rather than above)
    or
    b) work from last day back by subtracting, rather than adding, one day (from date in cell above) -- i.e. 2nd parameter would be -1 rather than 1

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    Does the form in a1 change much?

    If it does you could use vba to do this but it seems overkill. Why not click on a1 press format painter, click on a2, write the formula and copy down?

  6. #6
    Registered User
    Join Date
    10-05-2020
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    I prefer not to use VBA. I will use the format painter.
    Thanks again.

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

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    If C1 store the first day of the month,i.e, "01-Oct-2020"

    If A2 is expected to be the closest Saturday:

    A2:
    Please Login or Register  to view this content.
    If A2 is expected to be the closest Tue, Wed, Fri or Sat, which comes first (i.e, for Nov, first Tue = 03-Nov-2020):

    Please Login or Register  to view this content.
    A3:

    Please Login or Register  to view this content.
    Drag A3 down
    Quang PT

  8. #8
    Registered User
    Join Date
    10-05-2020
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Date pattern help: Tuesday, Wednesday, Friday, Saturday

    Thanks bebo021999 for your response.

+ 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] Workday: date when we need to close the ticket and we have Friday and Saturday off
    By follow7771 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2020, 12:18 PM
  2. Find previous Saturday and next friday dates given a date
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-31-2016, 08:48 AM
  3. [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
  4. 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
  5. How to show only Working Day on expire date (Friday + 2 = Tuesday)
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2013, 01:01 PM
  6. Formula return following Wednesday or Friday date.
    By mikeburg in forum Excel General
    Replies: 11
    Last Post: 06-30-2010, 02:04 PM
  7. Replies: 5
    Last Post: 04-10-2005, 09:06 AM

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