separate cell values with formulas - can this be improved?

[ilia]

OK, bear with me because this is kind of obfuscated.

I am working with a monthly invoice. On the invoice, we have Period
Start and Period End date. These will usually be first and last of
the month. We are billing for a service whose rate varies based on
whether it is a weekend or a weekday. We receive information from
service department regarding which days services were not performed,
and list them on the invoice.

Here's an example (without the " "s):

(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"

Now, we need to adjust number of billable days based on whether each
day is a weekday or a weekend.

In cells J1:J31, i have this formula: =MID($F
$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-
IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))

This extracts each number (1,2,3,6,9,10,12,15). Now, in the main
portion of the invoice I have these two array formulas:

=NETWORKDAYS(B22,D22)-SUM(IFERROR(--
(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)<6),""))&" Weekdays
(10hrs per day)"

and

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--
(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)>5),""))&" Weekends
(16hrs per day)"

My question is: can I do this somehow without using the helper column
(J)? I couldn't think of another way to find each subsequent comma,
perhaps there is a more clever way of using the SUBSTITUTE function?

I would like this to be a template that anyone can use, and have to do
nothing besides fill in the two dates and the non-service days. While
it's not a problem hiding the J column, there's still a chance it
might get deleted or rows inserted to screw up the calculation.

 

[Héctor Miguel]

hi, ilia !

[I guess] you could by-pass the helper column (J) and *construct* an array constant by...
using named-formulae [insert > name > define...] and the ancient 'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]

name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")

note the signs '!', it is important !!!

now, you can substitute in your array-formulae any reference to range J1:J31 with *the name* [nDays]

NOTES:

a) there is a possibe 'risk' of an xl-crash... while defining names as suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000] so...
use this proposal on your own... risk, criteria, modifications, etc.

b) also, if you need this procedure to be used in NON-english xl versions -?-
you will need to find out the character for rows separator in constant arrays by...
defining another named formula [i.e.]

name: rS
formula: =index(get.workspace(37),15)

and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search(" ",!$f$22)-1),",",rs)&"}")

[just wild ideas]
hth,
hector.

__ original post __

 

[T. Valko]

You can eliminate the helper column but the resulting formula becomes kind
of long. Also, I see you're not using the Holidays argument to NETWOKDAYS.
If you needed to account for holidays this might push things over the edge!

Both are array formulas:

=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1)))&"
Weekends(16hrs per day)"

 

[T. Valko]

Or, you can use these non-array versions (normally entered):

=NETWORKDAYS(B22,D22)-SUMPRODUCT(--(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22))),--(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUMPRODUCT(--(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22))),--(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5))&"
Weekends(16hrs per day)"

 

[Héctor Miguel]

hi, guys !

there is someting inaccurate in both formulae (OP & Biff) -?-
as OP says... there are only 8 *total* days: -> F22 = "1,2,3,6,9,10,12,15 = 8 days)"

Both formulae returns 22 *weekdays* -?-

Biff's formula returns only 1 weekend...
I guess it's missing day 1 -Sunday- because is not being preceeded by comma -?-

IF, we add to $F$22 one more weekday and one more weekend...
-> F22 = "1,2,3,6,9,10,12,15,19,21 = 10 days)"

both formulae continues returning 22 *weekdays*

OP's formula returns 0 weekends
Biff's formula returns -1 weekend

[something is still missing] :-(

regards,
hector.

__ previous posts __

 

[Héctor Miguel]

hi [again], guys !

I apologize... i made a wrong translation of weekday function into networkday :-((

[but]... I'm still getting more days than *total* days stated in $F$22 cell -?-

regards,
hector.

Héctor Miguel wrote in message ...
hi, guys !

there is someting inaccurate in both formulae (OP & Biff) -?-
as OP says... there are only 8 *total* days: -> F22 = "1,2,3,6,9,10,12,15 = 8 days)"

Both formulae returns 22 *weekdays* -?-

Biff's formula returns only 1 weekend...
I guess it's missing day 1 -Sunday- because is not being preceeded by comma -?-

IF, we add to $F$22 one more weekday and one more weekend...
-> F22 = "1,2,3,6,9,10,12,15,19,21 = 10 days)"

both formulae continues returning 22 *weekdays*

OP's formula returns 0 weekends
Biff's formula returns -1 weekend

[something is still missing] :-(

regards,
hector.

__ previous posts __

 

[Héctor Miguel]

hi [again], guys !

using the *tricky* named formula for nDays...

=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays (10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends (16 hrs per day)"

hth,
hector.

__ previous posts __

[I guess] you could by-pass the helper column (J) and *construct* an array constant by...
using named-formulae [insert > name > define...] and the ancient 'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]

name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")

note the signs '!', it is important !!!

now, you can substitute in your array-formulae any reference to range J1:J31 with *the name* [nDays]

NOTES:

a) there is a possibe 'risk' of an xl-crash... while defining names as suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000] so...
use this proposal on your own... risk, criteria, modifications, etc.

b) also, if you need this procedure to be used in NON-english xl versions -?-
you will need to find out the character for rows separator in constant arrays by...
defining another named formula [i.e.]

name: rS
formula: =index(get.workspace(37),15)

and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search(" ",!$f$22)-1),",",rs)&"}")

[just wild ideas]
hth,
hector.

__ original post __

I am working with a monthly invoice. On the invoice, we have Period Start and Period End date.
These will usually be first and last of the month.
We are billing for a service whose rate varies based on whether it is a weekend or a weekday.
We receive information from service department regarding which days services were not performed, and list them on the invoice.

Here's an example (without the " "s):

(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"

Now, we need to adjust number of billable days based on whether each day is a weekday or a weekend.

In cells J1:J31, i have this formula: =MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-IFERROR(FIND(CHAR(150),
SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))

This extracts each number (1,2,3,6,9,10,12,15).
Now, in the main portion of the invoice I have these two array formulas:
=NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)<6),""))&" Weekdays (10hrs per day)"
and
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J31),2)>5),""))&" Weekends (16hrs per day)"

My question is: can I do this somehow without using the helper column (J)?
I couldn't think of another way to find each subsequent comma, perhaps there is a more clever way of using the SUBSTITUTE function?

I would like this to be a template that anyone can use, and have to do nothing besides fill in the two dates and the non-service days.
While it's not a problem hiding the J column, there's still a chance it might get deleted or rows inserted to screw up the calculation.

 

[ilia]

OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.

=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))

This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).

The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.

By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.

-Ilia

hi [again], guys !

using the *tricky* named formula for nDays...

=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays (10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends (16 hrs per day)"

hth,
hector.

__ previous posts __

[I guess] you could by-pass the helper column (J) and *construct* an array constant by...
using named-formulae [insert > name > define...] and the ancient 'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]

name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")

note the signs '!', it is important !!!

now, you can substitute in your array-formulae any reference to range J1:J31 with *the name* [nDays]

NOTES:

a) there is a possibe 'risk' of an xl-crash... while defining names as suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000] so...
use this proposal on your own... risk, criteria, modifications, etc.

b) also, if you need this procedure to be used in NON-english xl versions -?-
you will need to find out the character for rows separator in constant arrays by...
defining another named formula [i.e.]

name: rS
formula: =index(get.workspace(37),15)

and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search(" ",!$f$22)-1),",",rs)&"}")

[just wild ideas]
hth,
hector.

__ original post __

- Show quoted text -

 

[Héctor Miguel]

hi, Biff !

I need to offer an excuse
your solution is simply perfect
I understood the post in a mistaken way

regards,
hector.

 

[T. Valko]

I found a bug in those formulas.

F22 = 1,2,3,6,9,10,12,15 = 8 days)

How is that string generated?

You would need to delimit each day with a comma from the rest of the string.
In the above there is no comma after the 15 and that is causing the bug. So,
F22 needs to look like this:

1,2,3,6,9,10,12,15, = 8 days)

Then, the slightly modified formulas:

=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1)))&"
Weekends(16hrs per day)"

--
Biff
Microsoft Excel MVP


OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.

=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))

This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).

The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.

By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.

-Ilia

hi [again], guys !

using the *tricky* named formula for nDays...

=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays
(10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends
(16 hrs per day)"

hth,
hector.

__ previous posts __

[I guess] you could by-pass the helper column (J) and *construct* an
array constant by...
using named-formulae [insert > name > define...] and the ancient
'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]

name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")

note the signs '!', it is important !!!

now, you can substitute in your array-formulae any reference to range
J1:J31 with *the name* [nDays]

NOTES:

a) there is a possibe 'risk' of an xl-crash... while defining names as
suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000]
so...
use this proposal on your own... risk, criteria, modifications, etc.

b) also, if you need this procedure to be used in NON-english xl
versions -?-
you will need to find out the character for rows separator in
constant arrays by...
defining another named formula [i.e.]

name: rS
formula: =index(get.workspace(37),15)

and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search("
",!$f$22)-1),",",rs)&"}")

[just wild ideas]
hth,
hector.

__ original post __

- Show quoted text -

 

[ilia]

Yes I see what you're saying. That string is typed in by hand, so a
comma can be added theoretically

Using this formula:

=NETWORKDAYS(B22,D22)-
SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),
2)<6,1)))

F22 = 1,2,3,6,9,10,12,15 = 8 days)

I get the correct result for weekdays, but only if F22 value begins
with the first day. 1 and 15 are weekend days; thhe rest are
weekdays. Month is July, so there is a total of 31 days. The result
I get is 16 weekdays, 7 weekends. This is correct.

If I add anything in front of the text, the first value is omitted
because of the comma added. But I don't see how adding a comma after
the last value affects this formula? The find function is looking for
anything preceded by the comma.

Another problem with it is in the case of a value being in the second
10 days. For instance, if

F2 = 12,15 = 2 days)

The formula will evaluate both ",1" and ",12" resulting on one extra
weekend (in case of July 2007, the 1st). The new formula does work at
avoiding this problem, so it looks like adding a comma at the end
might well be the solution.

Sorry, just brain dumping. This is an interesting problem and I'm
still figuring out what's the best way to set it up, so as to develop
accurate procedures for data entry.

Thanks all.

-Ilia

I found a bug in those formulas.

F22 = 1,2,3,6,9,10,12,15 = 8 days)

How is that string generated?

You would need to delimit each day with a comma from the rest of the string.
In the above there is no comma after the 15 and that is causing the bug. So,
F22 needs to look like this:

1,2,3,6,9,10,12,15, = 8 days)

Then, the slightly modified formulas:

=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22­)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT­(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1)­))&"
Weekends(16hrs per day)"

--
Biff
Microsoft Excel MVP


OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.

=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))

This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).

The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.

By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.

-Ilia

hi [again], guys !

using the *tricky* named formula for nDays...

=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays
(10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends
(16 hrs per day)"

hth,
hector.

__ previous posts __

[I guess] you could by-pass the helper column (J) and *construct* an
array constant by...
using named-formulae [insert > name > define...] and the ancient
'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]
name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")
note the signs '!', it is important !!!
now, you can substitute in your array-formulae any reference to range
J1:J31 with *the name* [nDays]
NOTES:
a) there is a possibe 'risk' of an xl-crash... while defining names as
suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000]
so...
use this proposal on your own... risk, criteria, modifications, etc.
b) also, if you need this procedure to be used in NON-english xl
versions -?-
you will need to find out the character for rows separator in
constant arrays by...
defining another named formula [i.e.]
name: rS
formula: =index(get.workspace(37),15)
and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search("
",!$f$22)-1),",",rs)&"}")
[just wild ideas]
hth,
hector.
__ original post __
I am working with a monthly invoice. On the invoice, we have Period
Start and Period End date.
These will usually be first and last of the month.
We are billing for a service whose rate varies based on whether it is a
weekend or a weekday.
We receive information from service department regarding which days
services were not performed, and list them on the invoice.
Here's an example (without the " "s):
(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"
Now, we need to adjust number of billable days based on whether each
day is a weekday or a weekend.
In cells J1:J31, i have this formula:
=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-IFERROR(FIND(CH­­AR(150),
SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))
This extracts each number (1,2,3,6,9,10,12,15).
Now, in the main portion of the invoice I have these two array
formulas:
=NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J­­31),2)<6),""))&"
Weekdays (10hrs per day)"
and
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MON­­TH(B22),J1:J31),2)>5),""))&"
Weekends (16hrs per day)"
My question is: can I do this somehow without using the helper column
(J)?
I couldn't think of another way to find each subsequent comma, perhaps
there is a more clever way of using the SUBSTITUTE function?
I would like this to be a template that anyone can use, and have to do
nothing besides fill in the two dates and the non-service days.
While it's not a problem hiding the J column, there's still a chanceit
might get deleted or rows inserted to screw up the calculation.- Hide
quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[T. Valko]

The probelm is/was that in my original formula the search criteria was
",number" (comma number).

With that criteria these would match a search of day 1:

1
10
11
12
etc

Basically *any* number that started with a 1.

By delimiting the days in the string with a comma from the rest of the
string solves that problem:

Going from: 1,2,3,6,9,10,12,15 = 8 days)

To: 1,2,3,6,9,10,12,15, = 8 days)

Solves that problem.

In the modified formula the search criteria is now ",number," (comma number
comma)

We might still be able to make it work with:

1,2,3,6,9,10,12,15 = 8 days)

But this will add more complexity to an already complex formula. But OTOH,
can you depend on users to comma delimit the string of days in this manner?

1,2,3,6,9,10,12,15, = 8 days)

--
Biff
Microsoft Excel MVP


Yes I see what you're saying. That string is typed in by hand, so a
comma can be added theoretically

Using this formula:

=NETWORKDAYS(B22,D22)-
SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),
2)<6,1)))

F22 = 1,2,3,6,9,10,12,15 = 8 days)

I get the correct result for weekdays, but only if F22 value begins
with the first day. 1 and 15 are weekend days; thhe rest are
weekdays. Month is July, so there is a total of 31 days. The result
I get is 16 weekdays, 7 weekends. This is correct.

If I add anything in front of the text, the first value is omitted
because of the comma added. But I don't see how adding a comma after
the last value affects this formula? The find function is looking for
anything preceded by the comma.

Another problem with it is in the case of a value being in the second
10 days. For instance, if

F2 = 12,15 = 2 days)

The formula will evaluate both ",1" and ",12" resulting on one extra
weekend (in case of July 2007, the 1st). The new formula does work at
avoiding this problem, so it looks like adding a comma at the end
might well be the solution.

Sorry, just brain dumping. This is an interesting problem and I'm
still figuring out what's the best way to set it up, so as to develop
accurate procedures for data entry.

Thanks all.

-Ilia

I found a bug in those formulas.

F22 = 1,2,3,6,9,10,12,15 = 8 days)

How is that string generated?

You would need to delimit each day with a comma from the rest of the
string.
In the above there is no comma after the 15 and that is causing the bug.
So,
F22 needs to look like this:

1,2,3,6,9,10,12,15, = 8 days)

Then, the slightly modified formulas:

=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22­)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT­(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1)­))&"
Weekends(16hrs per day)"

--
Biff
Microsoft Excel MVP


OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.

=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))

This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).

The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.

By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.

-Ilia

hi [again], guys !

using the *tricky* named formula for nDays...

=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&"
weekdays
(10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&"
weekends
(16 hrs per day)"

hth,
hector.

__ previous posts __

[I guess] you could by-pass the helper column (J) and *construct* an
array constant by...
using named-formulae [insert > name > define...] and the ancient
'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]
name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")
note the signs '!', it is important !!!
now, you can substitute in your array-formulae any reference to range
J1:J31 with *the name* [nDays]
NOTES:
a) there is a possibe 'risk' of an xl-crash... while defining names as
suggested...
if you copy the worksheets that uses them [at least, in xl-97 &
2000]
so...
use this proposal on your own... risk, criteria, modifications,
etc.
b) also, if you need this procedure to be used in NON-english xl
versions -?-
you will need to find out the character for rows separator in
constant arrays by...
defining another named formula [i.e.]
name: rS
formula: =index(get.workspace(37),15)
and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search("
",!$f$22)-1),",",rs)&"}")
[just wild ideas]
hth,
hector.
__ original post __
I am working with a monthly invoice. On the invoice, we have Period
Start and Period End date.
These will usually be first and last of the month.
We are billing for a service whose rate varies based on whether it is
a
weekend or a weekday.
We receive information from service department regarding which days
services were not performed, and list them on the invoice.
Here's an example (without the " "s):
(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"
Now, we need to adjust number of billable days based on whether each
day is a weekday or a weekend.
In cells J1:J31, i have this formula:
=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-IFERROR(FIND(CH­­AR(150),
SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))
This extracts each number (1,2,3,6,9,10,12,15).
Now, in the main portion of the invoice I have these two array
formulas:
=NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J­­31),2)<6),""))&"
Weekdays (10hrs per day)"
and
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MON­­TH(B22),J1:J31),2)>5),""))&"
Weekends (16hrs per day)"
My question is: can I do this somehow without using the helper column
(J)?
I couldn't think of another way to find each subsequent comma,
perhaps
there is a more clever way of using the SUBSTITUTE function?
I would like this to be a template that anyone can use, and have to
do
nothing besides fill in the two dates and the non-service days.
While it's not a problem hiding the J column, there's still a chance
it
might get deleted or rows inserted to screw up the calculation.- Hide
quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[ilia]

OK, I think I got it. I managed to achieve row independence from the
helper column version, enabling me to make it one big ugly array
formula. It assumes either a comma following the last value, or " = "
<space>=<space>.

For weekdays:
=NETWORKDAYS($B$22,$D$22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR($B
$22),MONTH($B$22),MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31)-1))
+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31))),FIND(" = ",$F$22,1))-
IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1))),2)<6),""))&" School Days (10hrs per day)"

For weekend days:
=($D$22-$B$22+1)-NETWORKDAYS($B$22,$D$22)-SUM(IFERROR(--
(WEEKDAY(DATE(YEAR($B$22),MONTH($B$22),MID($F
$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW(1:31))),FIND(" = ",$F$22,1))-
IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))
+1,1))),2)>5),""))&" Weekend days (16hrs per day)"

It's not as elegant as the others, and it still won't work if preceded
by text. Also, again it relies on Excel 2007 for IFERROR
functionality. However, I only see 5 levels of nesting so by
replacing IFERROR with IF(ISERROR()) it could probably work in earlier
versions.

Yes I see what you're saying. That string is typed in by hand, so a
comma can be added theoretically

Using this formula:

=NETWORKDAYS(B22,D22)-
SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22)),IF(WEEKD­AY(ROW(INDIRECT(B22&":"&D22)),
2)<6,1)))

F22 = 1,2,3,6,9,10,12,15 = 8 days)

I get the correct result for weekdays, but only if F22 value begins
with the first day. 1 and 15 are weekend days; thhe rest are
weekdays. Month is July, so there is a total of 31 days. The result
I get is 16 weekdays, 7 weekends. This is correct.

If I add anything in front of the text, the first value is omitted
because of the comma added. But I don't see how adding a comma after
the last value affects this formula? The find function is looking for
anything preceded by the comma.

Another problem with it is in the case of a value being in the second
10 days. For instance, if

F2 = 12,15 = 2 days)

The formula will evaluate both ",1" and ",12" resulting on one extra
weekend (in case of July 2007, the 1st). The new formula does work at
avoiding this problem, so it looks like adding a comma at the end
might well be the solution.

Sorry, just brain dumping. This is an interesting problem and I'm
still figuring out what's the best way to set it up, so as to develop
accurate procedures for data entry.

Thanks all.

-Ilia

I found a bug in those formulas.

F22 = 1,2,3,6,9,10,12,15 = 8 days)

How is that string generated?

You would need to delimit each day with a comma from the rest of the string.
In the above there is no comma after the 15 and that is causing the bug.. So,
F22 needs to look like this:

1,2,3,6,9,10,12,15, = 8 days)

Then, the slightly modified formulas:

=NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT(B22&":"&D22­­)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6,1)))&"
Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IF(ISNUMBER(FIND(","&DAY(ROW(INDIRECT­­(B22&":"&D22)))&",",","&F22)),IF(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5,1­)­))&"
Weekends(16hrs per day)"

"ilia" <[email protected]> wrote in message

OK, first a correction to my original helper column. I'm making heavy
use of the new IFERROR function, so bear with me if you're using an
earlier version.

=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW())),FIND(CHAR(150),SUBSTITUTE($F$22," =
",CHAR(150),1)))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F
$22,",",CHAR(150),ROW()-1))+1,1))

This assumes that if a comma is not found, the next separator will be
" = ". There are two problems with this: (1) it relies on this list
always starting in the first row, and (2) it is unable to omit any
text prior to the first day number (in case I want to combine E22 and
F22, for example).

The two array formulas proposed instead work (thanks Biff). I think
I'm going to end up doing that. I couldn't get the non-array versions
to work.

By the way, I'm not accounting for holidays because this is a 24/7/365
service (366 this fiscal year). I do use the holidays argument for
NETWORKDAYS on other types of invoices.

-Ilia

On Aug 8, 4:23 am, "Héctor Miguel" <[email protected]>
wrote:

hi [again], guys !
using the *tricky* named formula for nDays...
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)<6))&" weekdays
(10 hrs per day)"
=sumproduct(--(weekday(date(year(b22),month(b22),ndays),2)>5))&" weekends
(16 hrs per day)"
hth,
hector.
__ previous posts __
[I guess] you could by-pass the helper column (J) and *construct* an
array constant by...
using named-formulae [insert > name > define...] and the ancient
'evaluate' xl4-macro function [i.e.]
[assuming you need ONLY the *days* portion of a single-cell $F$22]
name: nDays
formula: =evaluate("{"&left(!$f$22,search(" ",!$f$22)-1)&"}")
note the signs '!', it is important !!!
now, you can substitute in your array-formulae any reference to range
J1:J31 with *the name* [nDays]
NOTES:
a) there is a possibe 'risk' of an xl-crash... while defining namesas
suggested...
if you copy the worksheets that uses them [at least, in xl-97 & 2000]
so...
use this proposal on your own... risk, criteria, modifications, etc.
b) also, if you need this procedure to be used in NON-english xl
versions -?-
you will need to find out the character for rows separator in
constant arrays by...
defining another named formula [i.e.]
name: rS
formula: =index(get.workspace(37),15)
and change the formula for nDays name to:
=evaluate("{"&substitute(left(!$f$22,search("
",!$f$22)-1),",",rs)&"}")
[just wild ideas]
hth,
hector.
__ original post __
I am working with a monthly invoice. On the invoice, we have Period
Start and Period End date.
These will usually be first and last of the month.
We are billing for a service whose rate varies based on whether itis a
weekend or a weekday.
We receive information from service department regarding which days
services were not performed, and list them on the invoice.
Here's an example (without the " "s):
(stored as dates)
B22 = July 1, 2007
D22 = July 31, 2007
(text entries)
E22 = "(Off on July "
F22 = "1,2,3,6,9,10,12,15 = 8 days)"
Now, we need to adjust number of billable days based on whether each
day is a weekday or a weekend.
In cells J1:J31, i have this formula:
=MID($F$22,IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))
+1,1),FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW()))-IFERROR(FIND(CH­­­AR(150),
SUBSTITUTE($F$22,",",CHAR(150),ROW()-1))+1,1))
This extracts each number (1,2,3,6,9,10,12,15).
Now, in the main portion of the invoice I have these two array
formulas:
=NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MONTH(B22),J1:J­­­31),2)<6),""))&"
Weekdays (10hrs per day)"
and
=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR(B22),MON­­­TH(B22),J1:J31),2)>5),""))&"
Weekends (16hrs per day)"
My question is: can I do this somehow without using the helper column
(J)?
I couldn't think of another way to find each subsequent comma, perhaps
there is a more clever way of using the SUBSTITUTE function?
I would like this to be a template that anyone can use, and have to do
nothing besides fill in the two dates and the non-service days.
While it's not a problem hiding the J column, there's still a chance it
might get deleted or rows inserted to screw up the calculation.- Hide
quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[ilia]

I could just have a validation rule that looks for a comma just before
the " = ". But yes, this is not a problem, I just like making things
more complicated I guess.

 

[Harlan Grove]

Or, you can use these non-array versions (normally entered):

=NETWORKDAYS(B22,D22)-SUMPRODUCT(--(ISNUMBER(FIND(","
&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22))),
--(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)<6))
&" Weekdays(10hrs per day)"

=(D22-B22)+1-NETWORKDAYS(B22,D22)-SUMPRODUCT(--(ISNUMBER(FIND(","
&DAY(ROW(INDIRECT(B22&":"&D22))),","&F22))),
--(WEEKDAY(ROW(INDIRECT(B22&":"&D22)),2)>5))
&" Weekends(16hrs per day)"

....

Try F22: 11 = 1 days)

in which case the formulas above will match 1 and 11 and return

21 Weekdays(10hrs per day)
8 Weekends(16hrs per day)

rather than the correct

21 Weekdays(10hrs per day)
9 Weekends(16hrs per day)

You need ending commas too. And you could avoid array entry and
volatile functions with

=SUMPRODUCT((ISERR(FIND(","&DAY(ROW(INDEX($1:$65536,B22,1)
:INDEX($1:$65536,D22,1)))&",",","&SUBSTITUTE(F22," =",","))))
*(WEEKDAY(ROW(INDEX($1:$65536,B22,1):INDEX($1:$65536,D22,1)),2)<6))
&" Weekdays (10hrs per day)"

=SUMPRODUCT((ISERR(FIND(","&DAY(ROW(INDEX($1:$65536,B22,1)
:INDEX($1:$65536,D22,1)))&",",","&SUBSTITUTE(F22," =",","))))
*(WEEKDAY(ROW(INDEX($1:$65536,B22,1):INDEX($1:$65536,D22,1)),2)>5))
&" Weekend Days (16hrs per day)"

 

[T. Valko]

...

Try F22: 11 = 1 days)

in which case the formulas above will match 1 and 11 and return

21 Weekdays(10hrs per day)
8 Weekends(16hrs per day)

rather than the correct

21 Weekdays(10hrs per day)
9 Weekends(16hrs per day)

You need ending commas too. And you could avoid array entry and
volatile functions with

=SUMPRODUCT((ISERR(FIND(","&DAY(ROW(INDEX($1:$65536,B22,1)
:INDEX($1:$65536,D22,1)))&",",","&SUBSTITUTE(F22," =",","))))
*(WEEKDAY(ROW(INDEX($1:$65536,B22,1):INDEX($1:$65536,D22,1)),2)<6))
&" Weekdays (10hrs per day)"

=SUMPRODUCT((ISERR(FIND(","&DAY(ROW(INDEX($1:$65536,B22,1)
:INDEX($1:$65536,D22,1)))&",",","&SUBSTITUTE(F22," =",","))))
*(WEEKDAY(ROW(INDEX($1:$65536,B22,1):INDEX($1:$65536,D22,1)),2)>5))
&" Weekend Days (16hrs per day)"

Yeah, I found my bug and made corrections/suggestions for it in the other
branch of this thread. I also thought about using ROW(INDEX rather than
ROW(INDIRECT.

The OP seems to have things under control but I was also thinking of
replacing ","&F22 with ","&LEFT(F22,FIND(" ",F22)-1)&"," but I like your use
of SUBSTITUTE.

 

[Harlan Grove]

. . . but I was also thinking of replacing ","&F22 with
","&LEFT(F22,FIND(" ",F22)-1)&"," but I like your use of SUBSTITUTE.

Thanks, but there should probably be another SUBSTITUTE call to guard
against irregular spaces.

FIND(","&DAY(ROW(INDEX($1:$65536,B22,1)
:INDEX($1:$65536,D22,1)))&",",
","&SUBSTITUTE(SUBSTITUTE(F22," ",""),"=",","))