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

✔ 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

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.

MIKE

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??

Or you have have another suggestion??

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, etcThe =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 M7See how that works.

MIKE

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!!

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

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

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

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

I am going to try it and will let you know soon....THANK YOU SO MUCH!!

IT WORKED!! You are an angel!!!

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History