Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts

    Adding Lastworkday Date

    Is there any way to automatically add the last working day of the month to a sheet. The sheet is made of days and dates for each month and when I submit the sheet I would, if possible have the date filled in automaically.

    Regards

    Alan

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    If you mean the last weekday of the month, try this in a cell.
    =IF(WEEKDAY(EOMONTH(NOW(),0),2)<6,EOMONTH(NOW(),0) ,IF(WEEKDAY(EOMONTH(NOW(),0),2)>5,EOMONTH(NOW(),0)-WEEKDAY(EOMONTH(NOW(),0),2)+5))

    It finds the day value for the last day of the month and if it's a weekend it subtracts the weekend day(s).

    cheers, Paul

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    If your work week isn't Mon-Fri here's a UDF that you can adjust to your workweek by changing the values in the If stmt as noted:
    Code:
    Option Explicit
    
    Public Function dteLastWorkDayThisMonth() As Date
    
       Dim dteLastDayOfMonth As Date
       Dim iWorkDay          As Integer
       Dim bCompleted        As Boolean
       
       If Month(Now()) < 12 Then
         dteLastDayOfMonth = Month(Now()) + 1 & "/1/" & Year(Now())
       Else
          dteLastDayOfMonth = "1/1/" & Year(Now()) + 1
       End If
       
       dteLastDayOfMonth = dteLastDayOfMonth - 1
       
       Debug.Print Format(dteLastDayOfMonth, "mm/dd/yy")
       
       Do
       
          iWorkDay = WorksheetFunction.Weekday(dteLastDayOfMonth)
          
          If iWorkDay > 1 And iWorkDay < 7 Then  '*** Assumes workweek is Mon-Fri! ***
            dteLastWorkDayThisMonth = dteLastDayOfMonth
            bCompleted = True
          Else
            dteLastDayOfMonth = dteLastDayOfMonth - 1
            bCompleted = False
          End If
          
       Loop Until bCompleted
       
    End Function  'dteLastWorkDayThisMonth
    Usage:
    lastworkingday.JPG

    Of course as written it assumes a workweek of Mon-Fri and in that case Paul's solution is better.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Here's the simple version of my formula.
    =EOMONTH(NOW(),0)-ROUND(WEEKDAY(EOMONTH(NOW(),0),2)^5/7776)

    cheers, Paul

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Simple for who? The first one I understood this one I have NO IDEA what it is doing. Would you care to enlighten us (or at least me)?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    What's so hard?
    We need the last week day so you format WEEKDAY to return Monday as 1 and Sunday as 7.
    Anything over 5 is a weekend and we need to subtract something to get back to Friday.
    As 5 is the magic number we multiply every WEEKDAY value to the power of 5, divide by Saturday (6^5 or 7776) and return the integer - I used ROUND but INT would probably have been better.
    Now subtract the integer from EOMONTH and you have the Friday, or retain the week day.

    cheers, Paul

  7. #7
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Thankyou both so much, I just have to convert it to Swedish then try them out.

  8. #8
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    I didnt explain it correctly - Sorry!
    The first formula works but in Junes worksheet is has the last workday of May (29-5-2015) as it does in Aprils worksheet. Is there anyway to modify the formula so it acts off the date of the sheet for example if the sheet has Junes date then the last working day of June etc. My working week is only ever Mon - Fri.

    Thanks again
    Alan

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Alan

    ..what about holidays???
    You could have the function give you the true last working day of the month by having a list of non-working dates e.g holidays, shut-down weeks etc etc.

    zeddy

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Yes the formula and macro can both be adjusted you just need to give us a cell address where we can be sure to find a date containing the desired month.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    The sheet I am using can be found at this thread: http://windowssecrets.com/forums/sho...R-Formula-Help as you can see I have two cells one with the month in and one with the year.
    Holidays I hadnt thought of, for example I shall start my holiday this coming Monday and my last working day would be the 5th June but I can use either that date or the last possible working day of June ( 30th ). I had been playing with trying to auto enter the last working day but the nearest I got was the end of the month each time which if it falls on a Saturday or a Sunday I wouldnt be working.

    Thanks for all your time and knowledge.

    Alan

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It would be simpler if you could use an actual date cell (you could have the other two cells calculate off that), but assuming your computer recognises text like "01 January 2015" as a date, you can use:
    =WORKDAY(EOMONTH(DATEVALUE("01 "&E4&" "&H4),0)+1,-1)

    You can also supply a list of holidays to WORKDAY.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    WORKDAY makes it even easier. I like the way you add a day to the end of the month to get the beginning of the next month, then use WORKDAY -1 to get the previous working day. No wonder you're a VIP!

    cheers, Paul

  14. #14
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by rory View Post
    It would be simpler if you could use an actual date cell (you could have the other two cells calculate off that), but assuming your computer recognises text like "01 January 2015" as a date, you can use:
    =WORKDAY(EOMONTH(DATEVALUE("01 "&E4&" "&H4),0)+1,-1)

    You can also supply a list of holidays to WORKDAY.
    Thanks Rory, in my workbook cell A11 is an actual date cell.

    Will try your formula and see how I get on.

    Many Thanks

    Alan

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In that case, just:
    =WORKDAY(EOMONTH(A11,0)+1,-1)
    Regards,
    Rory

    Microsoft MVP - Excel

  16. The Following User Says Thank You to rory For This Useful Post:

    AlanWade (2015-06-01)

Page 1 of 2 12 LastLast

Posting Permissions

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