Formula help

  • Thread starter Thread starter seanrigby
  • Start date Start date
S

seanrigby

have cell A1(formatted to DDDD to display name of day): =B1
Have cell A2(formatted to DDDD to display name of day): =B2
Etc. etc.

C1= 8
C2=8
c3=6
c4=9

Need a formula for column D to detect when the a cell in collumn A
=friday, then it'll switch to column E until it detects the next
Friday, then switch to collumn G. Basically add the total number up
until it reaches Friday, which is the end of the pay week, then the
next pay period starts on saturday until the next Friday, and give a
total number of hours.

Can you help?
 
you have only given us half the picture,i assume that col b is a column of
dates.
is column c where you put all the days hours?Do you actually need the four
or five columns or just four or five cells to show the sum of the hours for
each week?
 
Thanks for taking the time to help me:

Column A would be for the name of the week (i.e. monday, tuesday,
etc.)
Column B would be the actual date (i.e. 6/26/06, 6/27/06, etc.)
Column C would be for the straight hours worked.
Column D would be for the overtime hours worked.

Column E would list the first date of the particular week. Which will
usually be on a Saturday, unless the first week is not 7 days long.

Column F would list the last date of the particular week (which will
always be on a Friday)

Column G would total each week's straight hours, which starts on a
Saturday, and ends on a Friday. Which means there will be 4 or 5 rows
of Column E, due to there are 4-5 weeks in every given month.

Column H would total each week's overtime hours, which starts on a
Saturday and ends on a Friday. Which means there will be 4 or 5 rows
of Column F, due to there are 4-5 weeks in every given month.

This is for billing purposes, and our billing cycle ends the 25th of
every month, so the 26th is the start of a new billing cycle, which
also means the 26th is always going to fall on a different day, which
means the first week of any particular billing cycle may be shorter
than 7 days long, due to it always ending on a Friday. Same basically
goes for the last week of the billing cycle, since it always ends on
the 25th of every month.

I can figure out how to do Columns A-D, but the rest are confusing.

Here is an example, so maybe it is easier to understand. The letter
and numbers in parenthisis is the cell number:
----------------------------------------------------------------
Name of Week (a1) ; Date (b1) ; Straight Hours (c1) ; Overtime Hours
(d1) ;
Thursday (a2) ; 6/1/06 (b2) ; 8 (c2) ; 2 (d2)
Friday (a3) ; 6/2/06 (b3) ; 8 (c3) ; 1 (d3)
Saturday (a4) ; 6/3/06 (b4) ; 8 (c4) ; 1 (d4)
--------------------------------------------------------------------
From this above, formulas would determine the following:

(E1) From ; (F1) To ; (G1) Straight Hours ; (H1) Overtime Hours
(E2) 6/1/06 ; (F2) 6/2/06 ; (G2) 16 ; (H2) 3
(E3) 6/3/06 ; (F3) 6/3/06 ; (G3) 8 ; (H3) 1
 
whats your email.I can send you the sheet i started which always lists the
weeks in a month from the previous saturday.Might be helpful
 
Back
Top