Solved Can I write an if statement using networkday

February 6, 2013 at 11:17:39
Specs: Windows 7
 using excel - I need to create a if statement that will use a start date of 1/1/31 and an end date of 5/1/2013 but only report networkdays for Jan, feb, mar, etc...

See More: Can I write an if statement using networkday

February 6, 2013 at 16:59:35
 If you want it to show partial months, try this one:=IF(MONTH(E\$1)=MONTH(\$B\$2),NETWORKDAYS(E\$1,\$B\$2)*(\$C\$2*8),IF(E\$1<\$B\$2,NETWORKDAYS(E\$1,EOMONTH(E\$1,0))*(\$C\$2*8),""))It's one very long formula, cut & paste from here so you get it all.MIKEhttp://www.skeptic.com/

#1
February 6, 2013 at 12:46:41
 but only report networkdays for Jan, feb, mar, etcThe NETWORKDAYS() function will calculate the number of working days between two dates. Which means it returns a number.So, I'm not sure what it is your trying to do, please explain a bit more about what it is you want to accomplish.MIKEhttp://www.skeptic.com/

Report •

#2
February 6, 2013 at 13:14:25
 Thank you for your reply... okay, let me see if i can explain a little better. I have a start date of 1/1/13 and end date of 12/15/13. What I want is to calculate a rate of pay \$80 times workdays for each month. I wrote: =networkdays(date(2012,1,1)date(2013,1,31))*(M7*8) M7 = \$80 hourly rateThis helps me to see what the full pay for January will be \$14,720. but it does not use the Start date of 1/1/2013 and end date of 12/15/13 for all of the months.Am I making any sense??what I want to see if the full pay for Jan, Feb, Mar, Apr.... using the Start date of 1/1/2013 and end date of 12/15/2013.any ideas??

Report •

#3
February 6, 2013 at 13:18:25
 Or you have have another suggestion??

Report •

Related Solutions

#4
February 6, 2013 at 14:19:23
 You seem to almost have it.Try this, the formula looks like this:=NETWORKDAYS(A1,EOMONTH(A1,B1))*(M7*8)By way of an explanation of what it does:In Cell A1 enter your Start Date, IE: 01/01/2013In Cell B1 enter the number of Months you want to calculate.As you can see, we're using the =EOMONTH() function which will show the last day of the month, a specified number of months in the future.So, for just JAN, enter a zero on cell B1for Jan + Feb enter 1for Jan + Feb + March enter 2etc, etcThe =NETWORKDAYS() will determine the numberof days between your Start Date and the End of the month,the number of months you have specified in cell B1then multiply that by your number in cell M7See how that works.MIKE

Report •

#5
February 6, 2013 at 15:04:32
 What you are saying makes sense excepted the "B1" see if you can read below.A! B1 C1 Total YR JAN FEB MAR Start End Rate Pay 1/1/13 4/15/13 \$80 \$34,880 \$14,720 \$12,800 \$7,360BTW!! Thank you so much to taking the time to help me...you are extremely kind!!

Report •

#6
February 6, 2013 at 16:11:24
 If you are manually entering the column headers as months if you enter them as dates i.e. 1/1/13, 2/1/13 3/1/13 and then format them as "MMM" you can then have this equation under each of the month headers =IF(E1="","",IF(MONTH(E1)=MONTH(\$B\$2),NETWORKDAYS(E1,\$B\$2)*\$C\$2*8,NETWORKDAYS(E1,DATE(YEAR(E1),MONTH(E1),DAY(EOMONTH(E1,0))))*\$C\$2*8))this assumes Row 1 is for headersA2= Start dateB2=End DateC2=Rate (\$80)D2=Total year = SUM(F2:F14)E2 - H2 (assuming Jan-Apr) is where you would place the long calculation above```Start Date End Date Rate Total Year January February March April 1/01/2001 15/04/2013 \$80 \$48,000 \$14,720 \$12,800 \$13,440 \$7,040 ```@Mike - neat solution - I borrowed (stole) heavily from it and adapted.

Report •

#7
February 6, 2013 at 16:22:48
 Is this how your data is set up:``` A B C D E F G 1) Start End Rate Total Jan Feb Mar 2) 1/1/2013 4/15/2013 \$80 \$34,880 \$14,720 \$12,800 \$7,360 ```You want each Month's total to be displayed separately?You will need a formula for each month.The simplist way I can think of is on Row 1 enter the Datebut only display the Month.So in the above exampleCell E1 would contain 01/01/2013Cell F1 would contain 02/01/2013etc, etc.You can then Custom Format the cells to display only the Monthwith a Custom Format of:MMM for the short version IE: Janor MMMM for the complete month IE: JanuaryThen in Cell E2 enter the formula:=NETWORKDAYS(E\$1,EOMONTH(E\$1,0))*(\$C\$2*8)You can then drag the formula right as many columns as needed.That should give you what your looking for.MIKE

Report •

#8
February 6, 2013 at 16:39:08
 Here is an improved version of the formula:=IF(E\$1<=\$B\$2,NETWORKDAYS(E\$1,EOMONTH(E\$1,0))*(\$C\$2*8),"")Does a bit more checking for which month to include.MIKE

Report •

#9
February 6, 2013 at 16:52:26
 I just noticed, do you want this to display a Daily figure?MIKEhttp://www.skeptic.com/

Report •

#10
February 6, 2013 at 16:59:35
 If you want it to show partial months, try this one:=IF(MONTH(E\$1)=MONTH(\$B\$2),NETWORKDAYS(E\$1,\$B\$2)*(\$C\$2*8),IF(E\$1<\$B\$2,NETWORKDAYS(E\$1,EOMONTH(E\$1,0))*(\$C\$2*8),""))It's one very long formula, cut & paste from here so you get it all.MIKEhttp://www.skeptic.com/

Report •

#11
February 7, 2013 at 06:37:18
 I am going to try it and will let you know soon....THANK YOU SO MUCH!!

Report •

#12
February 7, 2013 at 07:00:36
 IT WORKED!! You are an angel!!!

Report •