Split days of the month if new month begins

In cells A1:G1 I have days of the week and in cells A2:G2 I have the days of the month. In cells A3:G3 I have time worked each day. Cell H3 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 new month. Since a new month has started during this week the times have to be split. If during the week there is no new month, times wont need to be split up and the sum of A3:G3 would go into cell H3 and cell I3 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.5

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" <[email protected]> wrote in
message news:[email protected]...
>
> In cells A1:G1 I have days of the week and in cells A2:G2 I have the
> days of the month. In cells A3:G3 I have time worked each day. Cell H3
> 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 new
> month. Since a new month has started during this week the times have to
> be split. If during the week there is no new month, times wont need to
> be split up and the sum of A3:G3 would go into cell H3 and cell I3
> 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.5
>
>
> --
> azdps
> ------------------------------------------------------------------------
> azdps's Profile:
http://www.excelforum.com/member.php...o&userid=37199
> View this thread: http://www.excelforum.com/showthread...hreadid=570165
>

thanks bob.