Split days of the month if new month begins

A

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

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)
 

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

SumProduct by specific weeks of the month or year 0
number of months and days between two dates without approximation 3
Range of Days in a month 10
Assign each week number to corresponding month of the year 5
counting days worked per month 5
Days of the month 7
Total Working hours If 2
SumProduct by specific week in a month 8

Top