Excel 2003 - Help Please

mercman1969

Been trying to add my table to my posts, but cant even achieve that

Thanks for your input

Differences between my table and yours is

Your cell C currently shows it as a number - mine shows a date (formula something along the lines of date of issue + 28

Your cell D also shows a number - mine will also need to show a date (formula something along the lines of Cell C + 28

I note you have added an extra cell "Due Date". This could be a good way to go for me.....will consider it


I will need to stop the day counter once it has been returned to me - not got a clue how to achieve this

EchoLocation

mercman1969 wrote: »

I will need to stop the day counter once it has been returned to me - not got a clue how to achieve this

In my example you could just add another cell to Column F and you can enter something like 'Done' or 'Complete' when needed. Then change the formula in Column B to IF(F2<>"","",NETWORKDAYS(A2,TODAY() )) and the counter will be blank. Alternatively you could add a 'Completed Date' to Column F instead and change the above formula to take 'Completed Date' away from 'Date of Issue' and the counter could then show the number of days taken.

mercman1969

EchoLocation wrote: »

In my example you could just add another cell to Column F and you can enter something like 'Done' or 'Complete' when needed. Then change the formula in Column B to IF(F2<>"","",NETWORKDAYS(A2,TODAY() )) and the counter will be blank. Alternatively you could add a 'Completed Date' to Column F instead and change the above formula to take 'Completed Date' away from 'Date of Issue' and the counter could then show the number of days taken.

Having trouble visualising it....

Would it be too much to ask for you to show me as you have previously??

I will need to keep a log of the counter.....how many days it stopped at

Thank you very much, your help is greatly appreciated

EchoLocation

Try this.



I've added a Completed Date column, this will allow the counter to be stopped and display the days taken.
In a similar sense I've added an argument to display if counter is 'overdue' which will take into account any extension days added.

Also, because it's a volatile function, if you are going to be using the TODAY() function in a lot of formulas it's best to add it to a separate cell out side your table and then replace it in the formulas with the cell reference. So if you add TODAY() to cell A1, then replace TODAY() in the formulas with A1.

mercman1969

EchoLocation wrote: »

Try this.



I've added a Completed Date column, this will allow the counter to be stopped and display the days taken.
In a similar sense I've added an argument to display if counter is 'overdue' which will take into account any extension days added.

Also, because it's a volatile function, if you are going to be using the TODAY() function in a lot of formulas it's best to add it to a separate cell out side your table and then replace it in the formulas with the cell reference. So if you add TODAY() to cell A1, then replace TODAY() in the formulas with A1.

PERFECT........that looks EXACTLY what i need

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

What are the forulas that need putting into each column?

victor2

Expanding on EchoLocation's fine example from #11 a bit...


Although created in Excel 2010, I've tested it in Excel 2000 and it still works.
Setting all of column E as listing holidays is fine with blank cells, but gives an error if any text is in that column. You could of course use a named range if you preferred.

mercman1969

victor2 wrote: »

Expanding on EchoLocation's fine example from #11 a bit...


Although created in Excel 2010, I've tested it in Excel 2000 and it still works.
Setting all of column E as listing holidays is fine with blank cells, but gives an error if any text is in that column. You could of course use a named range if you preferred.

WOW....now that has confused me.....

Am happy to just exclude weekends....did think about holidays, but thought it would get too complex....

victor2

mercman1969 wrote: »

WOW....now that has confused me.....

Am happy to just exclude weekends....did think about holidays, but thought it would get too complex....

Sorry. Didn't mean to!
Just illustrates holidays can be accounted for without too much extra work, but it could be a bit OTT.

mercman1969

victor2 wrote: »

Sorry. Didn't mean to!
Just illustrates holidays can be accounted for without too much extra work, but it could be a bit OTT.

Its ok.....I am beginning to uderstand what a powerful tool excel can be....

IF only i could get to grips with it:cool:

Sooler

Mirno wrote: »

A proper holiday version will require a loop

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