Counting Dates?

A

Aviator

=COUNTIF(A1:A100,"<" & NOW())

This works good for specific cells. I have a similar question.

How would I do the same thing given that:
1) You only have a starting date
2) Your only wanting to count work days (in this case 4 day/wk) up to NOW()

Thank you for all your help.

Aviator
 
R

Ron Rosenfeld

=COUNTIF(A1:A100,"<" & NOW())

This works good for specific cells. I have a similar question.

How would I do the same thing given that:
1) You only have a starting date
2) Your only wanting to count work days (in this case 4 day/wk) up to NOW()

Thank you for all your help.

Aviator
Click to expand...

If you were using a five day work week, you could use the NetWorkdays function
in the Analysis Tool Pak.

But since you are using a four day work week, you need a different solution.

If your workdays are Monday, Tuesday, Wednesday and Thursday, then the
following formula might work for you:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate&":"&TODAY())))={2,3,4,5}))

Note that the array constant is where you enter the weekdays (with Sun = 1) for
your workdays.


--ron
 
D

Domenic

Also, if you want to exclude holidays that fall within your 4 day work
week, you can add to Ron's formula. Try the following...

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&TODAY())))={2,3,4,5})*(1-ISNUMBE
R(MATCH(ROW(INDIRECT(A1&":"&TODAY())),B1:B10,0))))

....where A1 contains your start date, and B1:B10 contain your list of
holidays.

Note that the formula includes the current date in its calculation. To
exclude it from your calculation, try the following...

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&TODAY()-1)))={2,3,4,5})*(1-ISNUM
BER(MATCH(ROW(INDIRECT(A1&":"&TODAY()-1)),B1:B10,0))))

Hope this helps!
 
A

Aviator

I had not considered the holidays. That worked out great. Is there a way to
skip the holidays in this formular:

=IF(E$23="","",IF(WEEKDAY(E$23+1,2)>5,E$23+1+(WEEKDAY(E$23+1)>2)*7-WEEKDAY(E$23+1)+2,E$23+1))

I have the days set up automatically but with this formula it still has the
holidays in.

Thank you,

Aviator
 
Top