Get the amount of working days in a month using the computer date

Thanks to all who have posted, and really appreciate the explanations to the code

Thanks all




So i need the spread sheet to calculate how many working days of the month have been worked, but want the code to automatically pull the date from my computers date.

Today is the 6th of November - and on a 5 day week basis we have only worked 3 of the 6 days (because 2 were a weekend and the 6th is not yet finished)

The help in these forums has been spot on and every time and i am very grateful for everyone's help

Thanks in advance!

Excel help 3.xlsx

hi there. is the file relevant to what you need? seems like you can use:
=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY())-1

DATE(YEAR(TODAY()),MONTH(TODAY()),1) gives you the year & month of today's date but on the 1st of the month. TODAY() gives you today's date of course. i then minus 1 because you don't want to count today. you could even have a list of holidays say in U1:U3, and then:=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY(),U1:U3)-1

Formula:

Please Login or Register  to view this content.

Start date: first day of the month
End Date: yesterday ... TODAY()-1
HolidayList: is a Named Range with the holiday dates.


Regards, TMS

It has been pointed out to me that there are dates when my formula will return a negative value. Thanks for being so discreet benishiryo.

It has to be said that all the formulae can do that given the right choice of date

If you put today's date, =TODAY(), into a cell rather than using it directly, it will a) avoid having to calculate the value several times and b) give you the opportunity to test the effect of different dates. Simply type a date into the cell (overwriting =TODAY()) to see what value it will return.

benishiryo:

Formula:

Please Login or Register  to view this content.

01/09/2013: -1 01/11/2013: 0


ben_hensel:

Formula:

Please Login or Register  to view this content.

01/09/2013: 0 01/11/2013: -2


tmshucks:

Formula:

Please Login or Register  to view this content.

01/09/2013: 0 01/11/2013: -2




So, you might want to test for that condition ... not sure what you want the answer to be. Guess it would be zero (0)?


Regards, TMS

Thanks for the rep

If a holiday range isn't required then you can put the current date as the holiday range to prevent any negative values, e.g. with today's date in A1 use

=NETWORKDAYS(EOMONTH(A1,-1)+1,A1,A1)