Results 1 to 3 of 3

Calculate the date of the FIRST MONDAY of a month from a known start date

Threaded View

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    50

    Calculate the date of the FIRST MONDAY of a month from a known start date

    Generally the following formula (copied from a thread , here, that is over 10 years old and now closed) works fine but then blows its brains out in certain years!

    DATE(YEAR(A13),MONTH(A13),7-1+(MOD(3+7-WEEKDAY(DATE(YEAR(A13),MONTH(A13),1)),7)))

    but it does not consistently and accurately return the correct date for the first Monday of the month where B36 contains the value 1/8/2043 (dd/mm/yyyy)

    I get the correct day show above by tweaking the formula to read

    DATE(YEAR(A13),MONTH(A13),1-1+(MOD(3+7-WEEKDAY(DATE(YEAR(A13),MONTH(A13),1)),7)))

    Cell A13 changes relative to each column as the year changes (A13,B13,C13,D13)

    but then I do not consistently get the correct date for the first Monday of August where the date changes by column by one year until 2046. I

    For example 2045 gives the date as 31 July 2045 (a Monday but the wrong month!)

    Any idea about how to fix this so that EVERY year correctly returns the correct date for the first Monday of August?

    Greatly appreciate the assistance!
    Attached Files Attached Files
    Last edited by USAOz; 07-14-2024 at 06:59 PM.

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 Calculate Leave as per Custom Start and End Date of the Month
    By pnasir in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-27-2022, 06:49 AM
  2. [SOLVED] Calculate how many days people have from Start date until End date from month to month
    By Mariaoskars in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 02-28-2022, 09:36 AM
  3. [SOLVED] Calculate between dates [Start Date] [Closed Date] but if no close date today()
    By brian_2me in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-11-2021, 09:36 AM
  4. Calculate hours per month based on Start and End Date
    By Dave1780 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2020, 07:09 AM
  5. [SOLVED] INPUT month and OUTPUT > Weeks in the month, Start date of Wk & Start date of Wk
    By RavindraK in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-06-2018, 10:56 PM
  6. Calculate Start & End Date & Month for Each Week In Fiscal Year
    By sarndt01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2014, 02:14 PM
  7. Calculate the 6th month from start date
    By isaching in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-25-2013, 10:54 PM

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