Split days of the month if new month begins

[azdps]

In cells A1:G1 I have days of the week and in cells A2:G2 I have th
days of the month. In cells A3:G3 I have time worked each day. Cell H
has total time worked from the 30th-31st days since its another month
Cell I3 has the total time worked from the 1st-5th days of the ne
month. Since a new month has started during this week the times have t
be split. If during the week there is no new month, times wont need t
be split up and the sum of A3:G3 would go into cell H3 and cell I
would be blank.

---A1---B1---C1---D1---E1---F1---G1-------H1------I1--
1-SAT--SUN--MON--TUE--WED--THUR-FRI------------------
2-30---31---01---02---03---04---05-------------------
3-2.5--2.0--3.5--1.5--1.0--2.5--2.0------4.5-----10.

 

[Bob Phillips]

In H3 use

=SUM(A3:INDEX(A3:G3,1,IF(MIN(IF(B2:G2<A2:F2,COLUMN(B2:G2)))=0,7,MIN(IF(B2:G2
<A2:F2,COLUMN(B2:G2)))-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

I3 just becomes

=SUM(A3:G3)-H3

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[azdps]

thanks bob.