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

Report •

✔ Best Answer
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.

MIKE

http://www.skeptic.com/



#1
February 6, 2013 at 12:46:41
but only report networkdays for Jan, feb, mar, etc

The 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.

MIKE

http://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 rate

This 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/2013

In 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 B1
for Jan + Feb enter 1
for Jan + Feb + March enter 2
etc, etc

The =NETWORKDAYS() will determine the number
of days between your Start Date and the End of the month,
the number of months you have specified in cell B1
then multiply that by your number in cell M7

See how that works.

MIKE

http://www.skeptic.com/


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,360

BTW!! 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 headers
A2= Start date
B2=End Date
C2=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 Date
but only display the Month.

So in the above example
Cell E1 would contain 01/01/2013
Cell F1 would contain 02/01/2013
etc, etc.

You can then Custom Format the cells to display only the Month
with a Custom Format of:
MMM for the short version IE: Jan
or
MMMM for the complete month IE: January

Then 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

http://www.skeptic.com/


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

http://www.skeptic.com/


Report •

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

MIKE

http://www.skeptic.com/


Report •

#10
February 6, 2013 at 16:59:35
✔ Best Answer
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.

MIKE

http://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 •

Ask Question