Solved

Work Days

Posted on 2013-05-31
4
329 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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