Excel 2003 - Help Please

victor2

Sooler wrote: »

NETWORKDAYS has an optional holidays parameter
http://office.microsoft.com/en-gb/excel-help/networkdays-HP005209190.aspx

As does WORKDAY, and was illustrated in post #17.

EchoLocation

mercman1969 wrote: »

NOW....next question is.........

What are the forulas that need putting into each column?

The formulas are included in the image in the appropriate column.

Once you are comfortable with, and understand, what you have, I would recommend looking at victor2's suggestion for including holiday dates to exclude. It's really not much extra work, just a named list of dates (Bank Holidays etc) to be excluded along with weekends.

mercman1969

MANy Thanks to EchoLocation and Victor 2.......

I been playing with it on and off most of the day and it works a treat.....

Did try (failed) to try the holiday thing.....

As we have general shut downs over the year that we know of at the beginning of the year (these change yearly) is this something I could add Victor 2??

Not able to understand it or alter the formula currently Victor, but it is something i would like to include


Thanks again folks, your help is greatly appreciated

victor2

mercman1969 wrote: »

As we have general shut downs over the year that we know of at the beginning of the year (these change yearly) is this something I could add Victor 2??

Not able to understand it or alter the formula currently Victor, but it is something i would like to include

The third option in the WORKDAY (and NETWORKDAYS) formula is the holidays. It can be a range of cells listing non-working days outside of weekends. In the example =WORKDAY(A5,B5,E:E), the result is the starting date in A5 plus the number of working days in B5, ignoring any dates listed in the range specified, which in this case is the whole of column E. Blank cells are ignored, but cells not containing a date value can result in an error.
You could use a named range instead of E:E, you can have the list of dates in a different sheet, lots you can do with it really.

mercman1969

victor2 wrote: »

The third option in the WORKDAY (and NETWORKDAYS) formula is the holidays. It can be a range of cells listing non-working days outside of weekends. In the example =WORKDAY(A5,B5,E:E), the result is the starting date in A5 plus the number of working days in B5, ignoring any dates listed in the range specified, which in this case is the whole of column E. Blank cells are ignored, but cells not containing a date value can result in an error.
You could use a named range instead of E:E, you can have the list of dates in a different sheet, lots you can do with it really.

I understood........not a single word of that victor 2

victor2

mercman1969 wrote: »

I understood........not a single word of that victor 2

At least you're honest about it!
If you've got quite some way with it, but want more help, you can post us a screenshot of a portion of your spreadsheet (like we've done already). Could be another challenge you don't really need though.
You can toggle the display of a worksheet between the results of formulae or the actual formulae themselves by pressing CTRL + ` (grave accent, below Esc on most UK keyboards). It might help explain things.
Of course, if it's working, then you might just want to leave well alone...

mercman1969

Well,

thanks to those who have helped on this thread, i now have a functioning excel document....

The data is perfect, but i was trying to add one more thing....not sure if it is possible

I believe there is something called 'conditioning formatting'?

To finish of this document, would it be possible to add a 'traffic light' system to the elapsed days counter...

This would give an easy way of seeing what was about to expire or had expired or been completed...

I would like, if it is possible to show the following in the elapsed days column

As a deadline approaches (from 7 days to deadline) to turn the cell Orange / Yellow

If the deadline has passed, turn the cell Red

Once the item has been completed, Turn the cell Green

Is this at all possible, given there is already a formula in the elapsed days cells?

victor2

Conditional formatting is certainly what you want. This example was done with Excel 2000 (conditional formatting is under the Format option). It's easier with later versions, but I suspect your Excel 2003 will be like 2000:


Note that the first criteria met is used for the formatting and subsequent ones are ignored, so the order is important.
The green "Completed" one is a bit more tricky as it relies upon there being a value in the adjacent cell.

Edit:
Just realised that is flawed! The really old ones are not highlighted. The principle is right though, you just have to tweak the conditions to suit your needs...

EchoLocation

Here's an update for you that incorporates the Holiday option, several statuses along with conditional formatting...



All the formulas are listed with their cell locations.

I have added a separate cell for today's date =TODAY() to save adding it to all the formulas. I have also made the cell a named range called 'Cell_Today' to make it obvious in the formulas.

I have added the Holiday list on this sheet as an example but as it is a named range I would hold the list on a separate sheet to keep it out the way - note the Name Box giving the range name 'Range_Holidays'.

If you copy the above please be sure to also setup the above named ranges. To set a named range simply click in a cell or highlight a range of cells then type the name in the Name Box (to the left of the formula bar) and be sure to press enter.

The status counts at the top of the sheet are simple COUNTIFS but I have made the cells C1:F1 dependants so you can change the text if you wish to be something else - 'Done' instead of 'Completed' etc. - although they still need to keep the same meaning. I have chosen 'Pending' to show when there are 7 days or less remaining and 'Working' to just indicate that the entry is working or in progress if you will.

Hopefully I haven't missed anything out, I'm sure you may have some more questions though.

EDIT: Just realised the COUNTA in B2 is over all of Column G and so includes the header. Please change it to either =COUNTA($G:$G)-1 or =COUNTA($G5:$G12)

victor2

We're amusing ourselves, but I'd bet mercman1969 will be totally confused if he tries to digest that lot!
Just to add to the mix, I suspect Excel 2003's conditional formatting is closer to Excel 2000. I only have 2000 and 2010, so can't actually verify that. MS did significantly improve it at some point along the way.

Does nicely illustrate the potential you have with Spreadsheets though. How I remember the early days with VisiCalc, before Lotus 1-2-3 hit the scene, never mind Excel. :rotfl: