?
Solved

Work Days

Posted on 2013-05-31
4
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 13

Expert Comment

by:Corey Scheich
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

650 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