I want to get the sum of a total number of fields on a weekly basis.

[bartlegend]

I have a form that states in 1 row a total number of hours a person worked on a day. Above this row i have a row where i place the date of the certain day this person worked these hours( with TODAY()).

example:
Date: 21-5-2006 23-5-2006 25-5-2006
hours worked daily: 3 , 5 , 4,

WHat i want is that iwant to generate the total number of hours a person worked weekly.
I want to generate this from the dates.

In short i want excell to look at these dates (for instance: 21-5-2006, 23-5-2006, 25-5-2006. ANd take these dates beginning from monday to calculate a week and than add the numbers that i place next to the hours worked a day row and add them up getting an hours worked a week total on the bottom.

Total number of hours worked this week: (if 21-5-2006 is monday than this number should be: 12)

And i want these number to change every week when a new week is beginning.


Can anyone point me in the right direction to work this out ?

[colofnature]

If your dates are in row 1, and hours worked are in row 2, put this in B3

=IF(WEEKDAY(B1,2)<WEEKDAY(A1,2),B2,A3+B2)

and copy it along to the end of the row. This will create a running total for each week.

[Ikaabod]

You can use a variation on this:
=IF(WEEKDAY(A1,2)=1,IF(B1-A1<7,"A1 is Monday and B1 is same week","A1 is Monday but B1 not same week"),"A1 is not Monday")

I have a form that states in 1 row a total number of hours a person worked on a day. Above this row i have a row where i place the date of the certain day this person worked these hours( with TODAY()).

example:
Date: 21-5-2006 23-5-2006 25-5-2006
hours worked daily: 3 , 5 , 4,

WHat i want is that iwant to generate the total number of hours a person worked weekly.
I want to generate this from the dates.

In short i want excell to look at these dates (for instance: 21-5-2006, 23-5-2006, 25-5-2006. ANd take these dates beginning from monday to calculate a week and than add the numbers that i place next to the hours worked a day row and add them up getting an hours worked a week total on the bottom.

Total number of hours worked this week: (if 21-5-2006 is monday than this number should be: 12)

And i want these number to change every week when a new week is beginning.


Can anyone point me in the right direction to work this out ?

[Bob Phillips]

Try this formula

=SUMPRODUCT(--($B1:$M1>=TODAY()-WEEKDAY(TODAY())+2),--($B1:$M1<TODAY()-WEEKD
AY(TODAY())+9),B2:M2)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bartlegend" <bartlegend.289m0z_1148395502.6446@excelforum-nospam.com> wrote
in message news:bartlegend.289m0z_1148395502.6446@excelforum-nospam.com...
>
> I have a form that states in 1 row a total number of hours a person
> worked on a day. Above this row i have a row where i place the date of
> the certain day this person worked these hours( with TODAY()).
>
> example:
> Date: 21-5-2006 23-5-2006 25-5-2006
> hours worked daily: 3 , 5 , 4,
>
> WHat i want is that iwant to generate the total number of hours a
> person worked weekly.
> I want to generate this from the dates.
>
> In short i want excell to look at these dates (for instance:
> 21-5-2006, 23-5-2006, 25-5-2006. ANd take these dates beginning
> from monday to calculate a week and than add the numbers that i
> place next to the hours worked a day row and add them up getting
> an hours worked a week total on the bottom.
>
> Total number of hours worked this week: (if 21-5-2006 is monday
> than this number should be: 12)
>
> And i want these number to change every week when a new week is
> beginning.
>
>
> Can anyone point me in the right direction to work this out ?
>
>
> --
> bartlegend
> ------------------------------------------------------------------------
> bartlegend's Profile:
http://www.excelforum.com/member.php...o&userid=34703
> View this thread: http://www.excelforum.com/showthread...hreadid=544726
>