+ Reply to Thread
Results 1 to 7 of 7

Generate the date of the second in each month

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    12

    Red face Generate the date of the second in each month

    Hello everyone,

    I would like some assitans in developing a script or a using standard forumlas in Excel which can help me generate the date (YYYYMMDD) of the second Monday in each month.

    Someone who has an idea?

    Regards,
    Mikael

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Generate the date of the second in each month

    Let assume B3 = a date
    Somewhere =DATE(YEAR(B3),MONTH(B3),1)-WEEKDAY(DATE(YEAR(B3),MONTH(B3),1),2)+15
    gives the date of the second Monday of the month for the selected date
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    04-09-2014
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Generate the date of the second in each month

    Thank you very much!

  4. #4
    Registered User
    Join Date
    04-09-2014
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Generate the date of the second in each month

    how to close my post?

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Generate the date of the second in each month

    "how to close my post? " see forum's rules at the bottom

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Generate the date of the second in each month

    Quote Originally Posted by PCI View Post
    =DATE(YEAR(B3),MONTH(B3),1)-WEEKDAY(DATE(YEAR(B3),MONTH(B3),1),2)+15
    This formula always gives a date in the range 9th - 15th but for 2nd instance it needs to be 8th - 14th otherwise when the month starts on a Monday you get the wrong result, e.g. in June 2015 second Monday is on the 8th not 15th. To get correct results in all cases you can use this formula

    =B3-DAY(B3)+15-WEEKDAY(B3-DAY(B3)+6)
    Audere est facere

  7. #7
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Generate the date of the second in each month

    Thank you DDL for the adjustment
    PCI

+ 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] Generate a code that changes automatically every month
    By neima in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2014, 11:04 AM
  2. Formula to generate last day of month
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-20-2012, 04:25 PM
  3. [SOLVED] Excel month dropdown list to generate day of week and date for selected month
    By aaaaaaaa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2010, 02:45 PM
  4. formula to generate month-to-date based on auto date
    By infinitysales in forum Excel General
    Replies: 1
    Last Post: 06-27-2008, 01:09 AM
  5. How to generate dates for a month?
    By Jim9980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2006, 08:17 AM
  6. [SOLVED] How do I generate a new sheet based on date/week/month?
    By Jay in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2006, 04:55 AM
  7. is there a formula to generate a calendar month date rather than .
    By lmurray in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2005, 12:06 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