+ Reply to Thread
Results 1 to 3 of 3

Return the tuesday before the 20th of a month before an effective date

  1. #1
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Return the tuesday before the 20th of a month before an effective date

    In Cell A1 I have a date 04/01/19 (this is called Effective Date
    In cell B1 I need to return a date that is a Tuesday before the 20th of the month prior to 04/01/19 (this is called Deployment Date)
    Also, if the Tuesday is only 2 days before the 20th, it has to go find the next earliest Tuesday.

    For example, the output for the 04/01/19 date should be 03/12/19, and not 03/19/19, because the 19th is only 1 day before the 20th.
    Last edited by Alphabex; 10-19-2018 at 03:47 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Return the tuesday before the 20th of a month before an effective date

    Please try at B1

    =EOMONTH(A1,-2)+18-WEEKDAY(EOMONTH(A1,-2)+18,13)

  3. #3
    Registered User
    Join Date
    01-15-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    96

    Re: Return the tuesday before the 20th of a month before an effective date

    looks good! thank you

+ 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] Formula to get weekday closest to 20th of the month
    By rizmomin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-20-2018, 06:32 PM
  2. [SOLVED] Formula to get 15th and 20th of the month if weekdays
    By rizmomin in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-13-2017, 02:53 PM
  3. [SOLVED] Need date to be 20th of month or nearest Friday if 20th is weekend
    By Ochimus in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2016, 03:37 AM
  4. [SOLVED] Problem With Find And Replace With The 20th Of The Month
    By Macro Fool in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-13-2014, 11:01 AM
  5. Replies: 3
    Last Post: 01-15-2011, 10:16 AM
  6. VBA to get 2nd Tuesday of month
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2008, 11:17 PM
  7. Replies: 4
    Last Post: 08-09-2006, 09:59 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