Counting Dates?
 

=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

On Thu, 6 Jan 2005 12:53:03 -0800, "Aviator"
wrote:

=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

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

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!

In article ,
"Aviator" wrote:

=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

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+(WEEK DAY(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

"Domenic" wrote:

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!

In article ,
"Aviator" wrote:

=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