calculating b-weekly pay

[xaveryptak]

I have a worksheet that calculates daily earnings based on number of
hours worked each day (hrs x rate). Each day worked is inputed in a
separate row. There is an irregular number of days worked per period.
What formula can I use to calculate a bi-weekly pay based on calendar
days inputed in each period, in such a way that I don't have to
manually adjust range of rows included in the calculation?

 

[Guest]

First make a column of pay periods. Assume the first period is Monday Jan 1
thru Sunday Jan 14, etc. Let’s say your dates are in column A and you daily
dollar totals are in column B. In an un-used column, say column G, enter:

=ROUNDDOWN((A1-39083)/14+1,0)

Format this as General and copy all the way down.

Then make a Pivot Table from columns B&G. See:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[Sandy Mann]

With the daily date in Column A and the daily earnings in Column F try:

=SUMPRODUCT((A1:A1000>=Start Date)*(A1:A1000<=End Date)*F1:F1000)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk