I
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.
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.