Chip Pearson's NewWorkDays formula

P

Pete Rooney

Good afternoon,
Don't know if anyone has looked at this on Chip Pearson's website, but try
as I might, I can't get the the more complex variant of the formula to work,
the one that allows you to specify two lists to exclude - a generic 1, 2, 3
list to represent Sunday, Monday, Tuesday etc and an additional list for any
other specific dates to be excluded. You can hard code the 1,2 3 etc into the
formula (Example 1) or make it refer to a range of up to 7 cells that can
contain these numbers (Example 2), but everything I try returns #N/A!

Here's what I have (entered as array formulae)

Example
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))

Example
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))

StartDate (B5) contains 01/08/2008, EndDate (C5) contains 31/08/2008
ExcludeDaysOfWeek (H5:H11) contains 1, 6 and 7 in H5, H6 and H7
Holidays (J5:J11) contains 02/08/2008 and 03/08/2008 in J5 and J6

Please can anyone help - this will be really useful to me, but I just can't
figure out what I'm doing wrong!

Thanks in advance

Pete
 
N

Niek Otten

Hi Pete,

You generate a #NA yourself in the formula; if Startdate is a number (dates are numbers) you assign NA() in your IF. Same for
EndDate

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Good afternoon,
| Don't know if anyone has looked at this on Chip Pearson's website, but try
| as I might, I can't get the the more complex variant of the formula to work,
| the one that allows you to specify two lists to exclude - a generic 1, 2, 3
| list to represent Sunday, Monday, Tuesday etc and an additional list for any
| other specific dates to be excluded. You can hard code the 1,2 3 etc into the
| formula (Example 1) or make it refer to a range of up to 7 cells that can
| contain these numbers (Example 2), but everything I try returns #N/A!
|
| Here's what I have (entered as array formulae)
|
| Example 1
|
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
|
| Example 2
|
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
|
| StartDate (B5) contains 01/08/2008, EndDate (C5) contains 31/08/2008
| ExcludeDaysOfWeek (H5:H11) contains 1, 6 and 7 in H5, H6 and H7
| Holidays (J5:J11) contains 02/08/2008 and 03/08/2008 in J5 and J6
|
| Please can anyone help - this will be really useful to me, but I just can't
| figure out what I'm doing wrong!
|
| Thanks in advance
|
| Pete
|
|
|
|
|
|
|
|
|
|
|
|
 
P

Pete Rooney

Hi, Niek,

Thanks for responding. I must be missing something here - what's the point
of a formula to work out the number of "allowable" days between two dates if
you can't use dates in the formula?

Ah well, it IS the end of the week - perhaps I'm just overtired!

Pete
 
F

Fred Smith

The problem I see is that Startdate is less than Enddate, which is what you
are checking for. Don't you want:

....EndDate<=StartDate...

rather than

....StartDate<=EndDate

Regads,
Fred.
 
N

Niek Otten

You can use dates. But don't test for it being a number and then reject it (as you do), because dates are actually numbers.

So, =IF(ISUMBER(date)=False will fail for any date

I suggest you implement your formula in its simplest form first, without any validity checks and the, if it works, add the
restrictions. You'll see where you fail.
Or use Tools>Formula auditing>Evaluate formula to see where the formula goes wrong (that's what I did)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi, Niek,
|
| Thanks for responding. I must be missing something here - what's the point
| of a formula to work out the number of "allowable" days between two dates if
| you can't use dates in the formula?
|
| Ah well, it IS the end of the week - perhaps I'm just overtired!
|
| Pete
|
| "Niek Otten" wrote:
|
| > Hi Pete,
| >
| > You generate a #NA yourself in the formula; if Startdate is a number (dates are numbers) you assign NA() in your IF. Same for
| > EndDate
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | Good afternoon,
| > | Don't know if anyone has looked at this on Chip Pearson's website, but try
| > | as I might, I can't get the the more complex variant of the formula to work,
| > | the one that allows you to specify two lists to exclude - a generic 1, 2, 3
| > | list to represent Sunday, Monday, Tuesday etc and an additional list for any
| > | other specific dates to be excluded. You can hard code the 1,2 3 etc into the
| > | formula (Example 1) or make it refer to a range of up to 7 cells that can
| > | contain these numbers (Example 2), but everything I try returns #N/A!
| > |
| > | Here's what I have (entered as array formulae)
| > |
| > | Example 1
| > |
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > |
| > | Example 2
| > |
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > |
| > | StartDate (B5) contains 01/08/2008, EndDate (C5) contains 31/08/2008
| > | ExcludeDaysOfWeek (H5:H11) contains 1, 6 and 7 in H5, H6 and H7
| > | Holidays (J5:J11) contains 02/08/2008 and 03/08/2008 in J5 and J6
| > |
| > | Please can anyone help - this will be really useful to me, but I just can't
| > | figure out what I'm doing wrong!
| > |
| > | Thanks in advance
| > |
| > | Pete
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| > |
| >
| >
| >
 
P

Pete Rooney

I copied it straight from the website and would even BEGIN to know how to
start changing it. Who knows, maybe the Master himself will see this and let
me know what's wrong!

Thanks for your help!

Pete
 
N

Niek Otten

Fred is right; the test for Startdate<=EndDate is the wrong way around.
I tested the other formula and that one worked OK. I then saw your longer formula and thought you had added code to it.
Sorry!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I copied it straight from the website and would even BEGIN to know how to
| start changing it. Who knows, maybe the Master himself will see this and let
| me know what's wrong!
|
| Thanks for your help!
|
| Pete
|
|
|
|
|
| "Niek Otten" wrote:
|
| > You can use dates. But don't test for it being a number and then reject it (as you do), because dates are actually numbers.
| >
| > So, =IF(ISUMBER(date)=False will fail for any date
| >
| > I suggest you implement your formula in its simplest form first, without any validity checks and the, if it works, add the
| > restrictions. You'll see where you fail.
| > Or use Tools>Formula auditing>Evaluate formula to see where the formula goes wrong (that's what I did)
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Hi, Niek,
| > |
| > | Thanks for responding. I must be missing something here - what's the point
| > | of a formula to work out the number of "allowable" days between two dates if
| > | you can't use dates in the formula?
| > |
| > | Ah well, it IS the end of the week - perhaps I'm just overtired!
| > |
| > | Pete
| > |
| > | "Niek Otten" wrote:
| > |
| > | > Hi Pete,
| > | >
| > | > You generate a #NA yourself in the formula; if Startdate is a number (dates are numbers) you assign NA() in your IF. Same
for
| > | > EndDate
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | > | Good afternoon,
| > | > | Don't know if anyone has looked at this on Chip Pearson's website, but try
| > | > | as I might, I can't get the the more complex variant of the formula to work,
| > | > | the one that allows you to specify two lists to exclude - a generic 1, 2, 3
| > | > | list to represent Sunday, Monday, Tuesday etc and an additional list for any
| > | > | other specific dates to be excluded. You can hard code the 1,2 3 etc into the
| > | > | formula (Example 1) or make it refer to a range of up to 7 cells that can
| > | > | contain these numbers (Example 2), but everything I try returns #N/A!
| > | > |
| > | > | Here's what I have (entered as array formulae)
| > | > |
| > | > | Example 1
| > | > |
| > | >
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > | > |
| > | > | Example 2
| > | > |
| > | >
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > | > |
| > | > | StartDate (B5) contains 01/08/2008, EndDate (C5) contains 31/08/2008
| > | > | ExcludeDaysOfWeek (H5:H11) contains 1, 6 and 7 in H5, H6 and H7
| > | > | Holidays (J5:J11) contains 02/08/2008 and 03/08/2008 in J5 and J6
| > | > |
| > | > | Please can anyone help - this will be really useful to me, but I just can't
| > | > | figure out what I'm doing wrong!
| > | > |
| > | > | Thanks in advance
| > | > |
| > | > | Pete
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
 
P

Pete Rooney

H O O R A Y !

Fred, you're right - it works!

Thank you both VERY much!

Now... who's going to tell Mr Pearson? :)

Have a good weekend, both!
 
N

Niek Otten

<Now... who's going to tell Mr Pearson? :)>

I will

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|H O O R A Y !
|
| Fred, you're right - it works!
|
| Thank you both VERY much!
|
| Now... who's going to tell Mr Pearson? :)
|
| Have a good weekend, both!
|
| "Niek Otten" wrote:
|
| > Fred is right; the test for Startdate<=EndDate is the wrong way around.
| > I tested the other formula and that one worked OK. I then saw your longer formula and thought you had added code to it.
| > Sorry!
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > |I copied it straight from the website and would even BEGIN to know how to
| > | start changing it. Who knows, maybe the Master himself will see this and let
| > | me know what's wrong!
| > |
| > | Thanks for your help!
| > |
| > | Pete
| > |
| > |
| > |
| > |
| > |
| > | "Niek Otten" wrote:
| > |
| > | > You can use dates. But don't test for it being a number and then reject it (as you do), because dates are actually
numbers.
| > | >
| > | > So, =IF(ISUMBER(date)=False will fail for any date
| > | >
| > | > I suggest you implement your formula in its simplest form first, without any validity checks and the, if it works, add the
| > | > restrictions. You'll see where you fail.
| > | > Or use Tools>Formula auditing>Evaluate formula to see where the formula goes wrong (that's what I did)
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | >
| > | > | > | Hi, Niek,
| > | > |
| > | > | Thanks for responding. I must be missing something here - what's the point
| > | > | of a formula to work out the number of "allowable" days between two dates if
| > | > | you can't use dates in the formula?
| > | > |
| > | > | Ah well, it IS the end of the week - perhaps I'm just overtired!
| > | > |
| > | > | Pete
| > | > |
| > | > | "Niek Otten" wrote:
| > | > |
| > | > | > Hi Pete,
| > | > | >
| > | > | > You generate a #NA yourself in the formula; if Startdate is a number (dates are numbers) you assign NA() in your IF.
Same
| > for
| > | > | > EndDate
| > | > | >
| > | > | > --
| > | > | > Kind regards,
| > | > | >
| > | > | > Niek Otten
| > | > | > Microsoft MVP - Excel
| > | > | >
| > | > | > | > | > | Good afternoon,
| > | > | > | Don't know if anyone has looked at this on Chip Pearson's website, but try
| > | > | > | as I might, I can't get the the more complex variant of the formula to work,
| > | > | > | the one that allows you to specify two lists to exclude - a generic 1, 2, 3
| > | > | > | list to represent Sunday, Monday, Tuesday etc and an additional list for any
| > | > | > | other specific dates to be excluded. You can hard code the 1,2 3 etc into the
| > | > | > | formula (Example 1) or make it refer to a range of up to 7 cells that can
| > | > | > | contain these numbers (Example 2), but everything I try returns #N/A!
| > | > | > |
| > | > | > | Here's what I have (entered as array formulae)
| > | > | > |
| > | > | > | Example 1
| > | > | > |
| > | > | >
| > | >
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > | > | > |
| > | > | > | Example 2
| > | > | > |
| > | > | >
| > | >
| >
=IF(OR(StartDate<=0,EndDate<=0,StartDate<=EndDate,ISNUMBER(StartDate)=FALSE,ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
| > | > | > |
| > | > | > | StartDate (B5) contains 01/08/2008, EndDate (C5) contains 31/08/2008
| > | > | > | ExcludeDaysOfWeek (H5:H11) contains 1, 6 and 7 in H5, H6 and H7
| > | > | > | Holidays (J5:J11) contains 02/08/2008 and 03/08/2008 in J5 and J6
| > | > | > |
| > | > | > | Please can anyone help - this will be really useful to me, but I just can't
| > | > | > | figure out what I'm doing wrong!
| > | > | > |
| > | > | > | Thanks in advance
| > | > | > |
| > | > | > | Pete
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | > |
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VS2008 (VB) - Retrieving records from Access DB By DATE 3
IF(ISNA.. or ISERROR? question.. 3
Working Days function copied down column 1
Formula to determine number of current records by week 6
when good formulas go bad 2
Match Data in Multiple Columns 2
Formula setup / OFFSET problem 1
help with Hyperlink formula setup 1

Top