EOMONTH formula calculation only work days
Using the following formula: =MAX(0,MIN(EOMONTH(E$19,0),$B24)-MAX(E$19,$A24)+1)
I can calculate between a start and end date but gives me the days of each month.
Start Date End Date Jan Feb Mar Apr May
1/31/14 2/11/14 1 11 0 0 0
2/17/14 2/19/14 0 3 0 0 0
3/14/14 5/9/14 0 0 18 30 9
I actually just want to calculate the week days or work days. At the moment it is calculating all the days in that month.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Top chart uses this formula: =IF(AND(D$1>=$A2,D$1<=$B2),NETWORKDAYS(D$1,EOMONTH(D$1,0)),"")
Bottom chart is hand calculated but I need a formula that calculates that.
Start Date End Date Jan-2014 Feb-2014 Mar-2014 Apr-2014 May-2014 Jun-2014 Jul-2014 Aug-2014 Sep-2014 Oct-2014 Nov-2014 Dec-2014
1/31/2014 2/11/2014 20
2/17/2014 2/19/2014
3/14/2014 5/9/2014 22 22
Start Date End Date Jan-2014 Feb-2014 Mar-2014 Apr-2014 May-2014 Jun-2014 Jul-2014 Aug-2014 Sep-2014 Oct-2014 Nov-2014 Dec-2014
1/31/2014 2/11/2014 1 7 0 0 0 0 0 0 0 0 0 0
2/17/2014 2/19/2014 0 3 0 0 0 0 0 0 0 0 0 0
3/14/2014 5/9/2014 0 0 8 22 7 0 0 0 0 0 0 0
Capture.PNG
I am sorry, this image should be more clear on what I was trying to display
Even better will be an example workbook(which i'll see tomorrow morning(it's 19:30 here!)
Attached is the work book
Sheet 1 is the image I had sent with the formula that I got form the other forum
LDW tab is what I am currently working on. I need all of these number to make bar graphs to show trends and such.
Thank you for the help!
In D2 and copy across and down,
=MAX(0, NETWORKDAYS(MAX($A2, D$1), MIN($B2, EOMONTH(D$1, 0))))
Entia non sunt multiplicanda sine necessitate
WONDERFUL!!!! thank you so much! this is exactly what I needed!!!
You're welcome.
There are various other allocation formulas in the workbook at https://app.box.com/shared/7xm2kt408n
One last question, if I do not have dates entered how do I have the formula read it as 0?
I am getting back ridiculous numbers when no dates are entered
=if(count($a2:$b2) < 2, "", MAX(0, NETWORKDAYS(MAX($A2, D$1), MIN($B2, EOMONTH(D$1, 0)))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks