Solved

Work Days

Posted on 2013-05-31
Medium Priority
348 Views
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
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

LVL 13

Expert Comment

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

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

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

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

Question has a verified solution.

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

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!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Suggested Courses
Course of the Month14 days, 8 hours left to enroll