Defining NETWORKDAY start count date

Question

I’m trying to get a cell to start counting down days left in the month, but only on or after the 1^st^ of the month. Prior to that, I want it to count from the first day of the month regardless. So if there are 21 work days in a given month, the cell will indicate "21" prior to the first of that month. After that, it will count remaining days.

I have the countdown part down, I'm just can't manage to define the start count date. Here are some examples to hopefully provide an idea of what I'm trying to accomplish:

=IF((K134>=4/1/2013),=NOW(),4/1/13), =IF((K134>=4/1/2013),NOW(),4/1/13), =IF(K134>=4/1/2013,NOW(),4/1/13),  these are three attempts I made that don’t work to give you an idea.

Any assistance from from the community would be greatly appreciated.

Steve

Answer 1

When I type that into the cell, the cell indicates no value, it just goes blank. Here's what I typed:

=IF(DAY(J133)=1,"",NETWORKDAYS(J133,EOMONTH(J133,0)))

J133 contained the date.

Since I don't completely understand the formula, I'm not sure but it seems like this may still only function as a one or the other calculation, It will either calculate from a specified date, or from the beginning or the month, but not both. I'm looking for a formula to do both, but only return the value for the lesser of the two calculations.

Answer 2

replace all TODAY() with the date you have. if you have a date in A1 for eg.

=IF(DAY(A1)=1,"",NETWORKDAYS(A1,EOMONTH(A1,0)))

Answer 3

Thank you both for your assistance, I really appreciate the help.

Harrow's link was really helpful but the info on the link doesn't seem to address the countdown feature I need following the first of the month, it appears to only calcultate total working days in a given month. I thought this was the answer, but I think I spoke too soon.

benishiro, the countdoun seems to be functioning, but I can't figure out how to get the formula you provided to perform calculations for future months, only the current month. How can I define a future start and end date?

Steve

Answer 4

hi Steve,

probably something like:

=IF(DAY(TODAY())=1,"",NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)))

that's provided your 1st day of the week starts on Mon & have a 5 day work week.  if you have a list of holidays, say in E1:E10, then:

=IF(DAY(TODAY())=1,"",NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),E1:E10))

Answer 5

Steve,

This should help you:

http://spreadsheets.about.com/od/excelfunctions/qt/080321networkda.htm

Good luck

Harrow