Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Work Days

I need to create formulas to get work days in the following criteria's:

Total Work Days (excluding holidays) YTD
Total Work Days (excluding holidays) YTD thru end of current Month
Total Work Days (excluding holidays) Current Year
Total Work Days (excluding holidays) QTD
Total Work Days (excluding holidays) QTD thru end of current Month
Total Work Days (excluding holidays) MTD
Total Work Days (excluding holidays) Total days for current month
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
1 Solution
 
Corey ScheichDeveloperCommented:
There are a number of variables you have to define like for each week day, is this day also a work day?  Many businesses work Saturdays and Sundays some don't work on Fridays and some even have half days on friday.  At a minimum this would require a list of weekdays with a Boolean IsWorkday and a maximum of PercentageWorked.   Also which holidays banker hours afford many more "Holidays" than a manufacturing job so the list of holidays will have to be provided also.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
We do not work weekends so no weekends. We work Monday thru Friday, excluding holidays.
I have a list of the holidays in my file that I can refer to so those dates get excluded.
0
 
pony10usCommented:
Try this:

Create a named range called holidays.  I used D1-D11 in sample

Put This formula in column.  I used A in sample

=NETWORKDAYS(B1,EOMONTH(B1,0),holidays)

Put dates in column.  I used B in sample



This will give you total workdays from given date until end of that month.  It isn't exactly what you are asking for but a good starting point.
ee-sample.xlsx
0
 
barry houdiniCommented:
For simplicity put today's date in a cell, e.g. A1 and then use these formulas assuming holidays as a named range:

Total Work Days (excluding holidays) YTD:
=NETWORKDAYS(DATE(YEAR(A1),1,1),A1,holidays)

Total Work Days (excluding holidays) YTD thru end of current Month
=NETWORKDAYS(DATE(YEAR(A1),1,1),EOMONTH(A1,0),holidays)

Total Work Days (excluding holidays) Current Year
=NETWORKDAYS(DATE(YEAR(A1),1,1),DATE(YEAR(A1),12,31),holidays)

Total Work Days (excluding holidays) QTD
=NETWORKDAYS(EOMONTH(A1,MOD(-MONTH(A1),3)-3)+1,A1,holidays)

Total Work Days (excluding holidays) QTD thru end of current Month
=NETWORKDAYS(EOMONTH(A1,MOD(-MONTH(A1),3)-3)+1,EOMONTH(A1,0),holidays)

Total Work Days (excluding holidays) MTD
=NETWORKDAYS(EOMONTH(A1,-1)+1,A1,holidays)

Total Work Days (excluding holidays) Total days for current month
=NETWORKDAYS(EOMONTH(A1,-1)+1,EOMONTH(A1,0),holidays)

regards, barry
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now