In , Marcel Marien
spake thusly:

here is a sample of how the data looks like now. The red numbers
represent the months and days of another calender. They reach
from 1-19.

17 01.01.1900 18 18 02.01.1900 18 19 03.01.1900 18
Sunday Monday Tuesday

Uh. "Red"? I am reading this in a standard Usenet newsreader.
There is no HTML, and there is no color. This is a text-only
medium. (I am reading this under NetBSD in Unix, albeit over an
ANSI terminal emulator on my Windowx XP box.)

I have added an extra row, so that I can use the weekday line to
check for a Sunday by writing:

=SUMPRODUCT(--(DayOfWeek<"Sunday");(--(DayOfWeek<0)))

but I am not totally happy with that solution, since I would like
to have the option of checking for several days at once (by using
<=) and I have found no formula to turn the spelled out weekdays
back into their corresponding numbers.

Can't you use either the WEEKDAY function or a LOOKUP/CHOOSE
approach?

In fact, it looks like Ron suggested exactly that when he wrote
(also reproduced in full down below for all you top-poster lovers):

=SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7)))

(Array formula.)

-dman-

================================================== =
"Ron Coderre" schrieb im Newsbeitrag
...
Marcel

If you can give a sampling of the kinds of values the cells would contain,
we can probably tailor a solution that meets your needs.

For instance, if the only numeric values will be either dates after Jan
01,
1990 or department numbers which are between 100 and 999, that scenario
can
be accommodated.

***********
Regards,
Ron

XL2002, WinXP


"Marcel Marien" wrote:

Dear Ron,

Thank you for your suggestion. You check in it whether the cell contains
a
number, but I need to check whether it contains a date, for example by
asking =CELL("Format";A1)="D1". If, however, I don't refer to a singel
cell,
but to a range of cells (=CELL("Format";A1:V1)="D1") the formular returns
only the formating value of the 1st cell in the range. - Do you know any
alternative or do you know what I am doing wrong?

Marcel

"Ron Coderre" schrieb im Newsbeitrag
...
Actually, if the range of cells may contain dates, text, blanks, or
errors....
Try this ARRAY FORMULA:


=SUM(IF(ISNUMBER(WEEKDAY(A1:A50-1)),--(WEEKDAY(A1:A50,2)<7)))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

=SUMPRODUCT((A1:A500)*(WEEKDAY(A1:A50)1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Marcel Marien" wrote:

Hello,

can anybody tell me how to formulate:

"Count all cells in a row which contain a date (not every cell
contains
a
date) that does not fall on a Sunday"?

Somehow I just don't seem to get it.
Thank you very much in advance,

Marcel