Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Networkdays?
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Correction --
The last line should be 'zero out those day that are not part of the SELECTED month.' S:) |
#5
|
|||
|
|||
On 4 Aug 2005 05:13:38 -0700, "Steve" wrote:
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
On 5 Aug 2005 04:09:48 -0700, "Steve" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS() not avaialbe after reopening | Excel Worksheet Functions | |||
NETWORKDAYS function problem | Excel Worksheet Functions | |||
networkdays | Excel Worksheet Functions | |||
Networkdays shows as #NAME even though I have the toolpack instal. | Excel Discussion (Misc queries) | |||
MS Excel Function - Networkdays | Excel Worksheet Functions |