Solved

Work Days

Posted on 2013-05-31
4
335 Views
Last Modified: 2013-05-31
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
Comment
Question by:Lawrence Salvucci
4 Comments
 
LVL 13

Expert Comment

by:Corey2
ID: 39210882
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39210891
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39210949
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39211168
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question