Page 1 of 2 12 LastLast
Results 1 to 10 of 12

First Monday in the Year

This is a discussion on First Monday in the Year within the Excel Questions forums, part of the Question Forums category; Dear All With the year in B4 (say 2014) could I trouble someone for a formula to show the first ...

  1. #1
    Board Regular
    Join Date
    Nov 2004
    Posts
    723

    Default First Monday in the Year

    Dear All

    With the year in B4 (say 2014) could I trouble someone for a formula to show the first Monday in the year.

    As ever my thanks

  2. #2
    Board Regular
    Join Date
    Oct 2012
    Posts
    182

    Default Re: First Monday in the Year

    Get this formula from someone...

    =DATE(YEAR(B4),1,8)-WEEKDAY(DATE(YEAR(B4),1,6))

  3. #3
    Board Regular
    Join Date
    Nov 2004
    Posts
    723

    Default Re: First Monday in the Year

    Hi cml19722000


    With 2014 in B4 I'm getting 02/01/1905

  4. #4
    New Member
    Join Date
    Sep 2012
    Posts
    14

    Default Re: First Monday in the Year

    Hi,

    You can also try below function prepared for you. Put it inside a module and use it as a excel formula from any cell.

    =DayDate("Monday",2013,1,1)

    You can also use the cell referrence like A1, B1 etc.


    Code:
    Function DayDate(DayToFind As String, yr As Integer, Mn As Integer, Dy As Integer) As String
        Dim Dt As Date
        Dim Dayy As String
        
        Dt = DateValue(yr & "/" & Mn & "/" & Dy)
        Dayy = Format(Dt, "DDDD")
        
        Do While Dayy <> DayToFind
        
        Dy = Dy + 1
        Dt = DateValue(yr & "/" & Mn & "/" & Dy)
        Dayy = Format(Dt, "DDDD")
        
        Loop
        
        DayDate = Format(Dt, "DD-MMM-YYYY")
    End Function
    Regards
    taps

  5. #5
    Board Regular
    Join Date
    Oct 2012
    Posts
    182

    Default Re: First Monday in the Year

    Hi,

    Your input need to be in "Date" format. Thanks.

  6. #6
    Board Regular
    Join Date
    Nov 2004
    Posts
    723

    Default Re: First Monday in the Year

    Taps

    Thank you that works a treat

    Just in case someone asks for a different first day in a year could you explain to an idiot how this actually works.

    Again my thanks

  7. #7
    New Member
    Join Date
    Sep 2012
    Posts
    14

    Default Re: First Monday in the Year

    Hi,

    You are most welcome.
    The above formula can be used to findout the first date of any days, any year and any month.

    Just you need to change the day (Monday...Sunday etc.), the year and the month like below.

    =DayDate("Friday",2014,2,1) will return back the first Friday in February in 2014.

    Regards
    taps

  8. #8
    Board Regular
    Join Date
    Nov 2004
    Posts
    723

    Default Re: First Monday in the Year

    taps

    What a real idiot.

    I was looking so intently at the code that I forgot the formula

    Again my sincere thanks for a very versatile piece of code

  9. #9
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644

    Default Re: First Monday in the Year

    There are several ways with standard formula, here's one.
    With your year in A2
    Code:
    =DATE(A2,1,1)+CHOOSE(WEEKDAY(DATE(A2,1,1),2),0,6,5,4,3,2,1)

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,378

    Default Re: First Monday in the Year

    Quote Originally Posted by lapta301 View Post
    Just in case someone asks for a different first day in a year could you explain to an idiot how this actually works.
    See my mini-blog article here for a general solution for any given month...

    Nth Such-And-Such Day Of The Month

    The article contains both a formula and code solution.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com