B
Bill Edwards
Hi group,
I have a spreadsheet where every row has a unique project. The last
two columns are "deadline date" and "actual completion date" none of
which fall on either a Saturday or a Sunday. These are columns J and K
and formatted as a standard date field.
The UK bank holidays for 2003 are just stored in the range $Q$2:$Q$9
and for the record are:
01-Jan-03
18-Apr-03
21-Apr-03
05-May-03
26-May-03
25-Aug-03
25-Dec-03
26-Dec-03
I need to find out which of my unique projects (in rows) were on time
(and by how much), similarly which missed their deadlines (and by how
much).
So L2=J2-K2 gives the lead/lag (+ = ahead, - = behind)
and M2=IF(K2<J2,"Hit",IF(K2=J2,"On Time","Miss"))
No problems there except I need to do this in terms of the working
week, excluding Saturdays and Sundays. I checked out the Excel help
file and was informed about the NETWORKDAYS (start, end, exclusions)
function in the Analysis Toolpak.
So my formula in N2=NETWORKDAYS(J2,K2,$Q$2:$Q$9)
Again, no problems there - and the results I have - on copying the
formulas in row 2 down columns L, M and N for as many rows as I need
to - all seem reasonable except I've noticed something peculiar when
the two dates being analysed differ by just -1.
The formula in L2 will say -1 but the formula in N2 will say -2. So
this got me thinking as regards could my entire analysis be incorrect?
Has anyone else noticed this and if so, how did you compensate for it?
If anyone thinks I'm mad, try it yourself for 19th and 20th March 2003
and see what I mean. Very odd indeed.
Bill
I have a spreadsheet where every row has a unique project. The last
two columns are "deadline date" and "actual completion date" none of
which fall on either a Saturday or a Sunday. These are columns J and K
and formatted as a standard date field.
The UK bank holidays for 2003 are just stored in the range $Q$2:$Q$9
and for the record are:
01-Jan-03
18-Apr-03
21-Apr-03
05-May-03
26-May-03
25-Aug-03
25-Dec-03
26-Dec-03
I need to find out which of my unique projects (in rows) were on time
(and by how much), similarly which missed their deadlines (and by how
much).
So L2=J2-K2 gives the lead/lag (+ = ahead, - = behind)
and M2=IF(K2<J2,"Hit",IF(K2=J2,"On Time","Miss"))
No problems there except I need to do this in terms of the working
week, excluding Saturdays and Sundays. I checked out the Excel help
file and was informed about the NETWORKDAYS (start, end, exclusions)
function in the Analysis Toolpak.
So my formula in N2=NETWORKDAYS(J2,K2,$Q$2:$Q$9)
Again, no problems there - and the results I have - on copying the
formulas in row 2 down columns L, M and N for as many rows as I need
to - all seem reasonable except I've noticed something peculiar when
the two dates being analysed differ by just -1.
The formula in L2 will say -1 but the formula in N2 will say -2. So
this got me thinking as regards could my entire analysis be incorrect?
Has anyone else noticed this and if so, how did you compensate for it?
If anyone thinks I'm mad, try it yourself for 19th and 20th March 2003
and see what I mean. Very odd indeed.
Bill
