Adding Lastworkday Date

Topic

New Reply

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

Reply | Quote

Replies

If you mean the last weekday of the month, try this in a cell.
=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

Reply | Quote

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())  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:
40850-lastworkingday

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Here’s the simple version of my formula.
=EOMONTH(NOW(),0)-ROUND(WEEKDAY(EOMONTH(NOW(),0),2)^5/7776)

cheers, Paul

Reply | Quote

Paul,

Simple for who? The first one I understood this one I have NO IDEA what it is doing. :confused: Would you care to enlighten us (or at least me)? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thankyou both so much, I just have to convert it to Swedish then try them out.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

The sheet I am using can be found at this thread: http://windowssecrets.com/forums/showthread//166153-If-And-OR-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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

In that case, just:
=WORKDAY(EOMONTH(A11,0)+1,-1)

Reply | Quote

In that case, just:
=WORKDAY(EOMONTH(A11,0)+1,-1)

Thanks Rory, works perfectly!

Alan

Reply | Quote