Networkdays?

[Steve]

I've had a look at Chip's site and think the answer to my problem will
lie in a Networkdays formula, but don't _know_. So...

Cells A3-A7 are Mon, Tues... Fri
Cell A1 contains the month, B1 the year.

What I want to do is see what day the 1st of the month falls on. I then
want to zero out all days prior to that in column B. From the 1st
onwards, I want to be able to input data.

So, for example, September 05 would look like:

A B
September 2005

Mon 0
Tues 0
Weds 0
Thurs
Fri

If the 1st was a Monday, as it is this month, for example, I would want
all B3-B7 left clear for input.

Does anyone know how I would do this?

Thanks in advance

Steve

 

[Guest]

hi,
here is the networkdays formula

=NETWORKDAYS($BY$3,BY37,$BW$2:$BW$4)
It canculate the number of days between 2 dates. which don't seem to be what
you're trying to do. You can use the formula =TEXT(BY32,"ddd") to display the
day of a certain date but in your example, you have no dates so i'm a little
confused. you might consider a hidden column of dates to allow the formulas
something to work from.

Post back with more info

Regards
FSt1

 

[Steve]

Hi

Thanks for replying.

The idea is that we have a monthly statistical period, but only work
from Monday - Friday.

You know how, if you look at a calendar, it's usually arrayed by day,
say Mon - Sun. Then you have the 1st under, say, Tuesday, the 2nd under
Wednesday, 3rd Thursday, and so on; then you also often have the last
days of the previous month highlighted or grayed out (or just a space,
or asterisk) in some way, eg, Monday 31st October 2005 may be shown as
a gray number on November 05's calendar.

I'm trying to replicate this principle. I want to have a Monday to
Friday permanently on the sheet, but a zero next to those days which
are not part of the current month.

Phew. I hope that made some sense.

Steve

 

[Steve]

Correction --

The last line should be 'zero out those day that are not part of the
SELECTED month.'

S

 

[Ron Rosenfeld]

I've had a look at Chip's site and think the answer to my problem will
lie in a Networkdays formula, but don't _know_. So...

Cells A3-A7 are Mon, Tues... Fri
Cell A1 contains the month, B1 the year.

What I want to do is see what day the 1st of the month falls on. I then
want to zero out all days prior to that in column B. From the 1st
onwards, I want to be able to input data.

So, for example, September 05 would look like:

A B
September 2005

Mon 0
Tues 0
Weds 0
Thurs
Fri

If the 1st was a Monday, as it is this month, for example, I would want
all B3-B7 left clear for input.

Does anyone know how I would do this?

Thanks in advance

Steve

I am assuming you wish to put data in column B. If that is the case, you
probably don't want to have formulas in those cells, otherwise you will erase
the formulas when you enter the data.

Perhaps conditional formatting would work for you. You could have the cells
"grayed out" of they precede the first of the month.

With the month and year in A1 and B1; and with the days of the week starting in
Row 3; try the following.

Select B3:B7
From the main menu bar: Format/Conditional Formatting
Formula Is:
=MOD(WEEKDAY(DATEVALUE($A$1&$B$1)),7)>(ROW()-1)
Format/Patterns and select some appropriate color (e.g. Grey).

<OK>
<OK>



--ron

 

[Steve]

Hi Ron

Thanks for that; I'd realised that the way to do this was with
conditional formatting and had done what I wanted with a whole heap of
helper cells. I'll try your method as it looks tidier.

Cheers

Steve

 

[Ron Rosenfeld]

Hi Ron

Thanks for that; I'd realised that the way to do this was with
conditional formatting and had done what I wanted with a whole heap of
helper cells. I'll try your method as it looks tidier.

Cheers

Steve

Glad to help. Thanks for the feedback.


--ron