|
Formula help
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? -- seanrigby ------------------------------------------------------------------------ seanrigby's Profile: http://www.excelforum.com/member.php...o&userid=34133 View this thread: http://www.excelforum.com/showthread...hreadid=555242 |
Formula help
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 ---------------------------------------------------------------- Sorry if this seems confusing. I don't know how else to state it. Thanks for your time and help! -- seanrigby ------------------------------------------------------------------------ seanrigby's Profile: http://www.excelforum.com/member.php...o&userid=34133 View this thread: http://www.excelforum.com/showthread...hreadid=555242 |
Formula help
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 -- paul remove nospam for email addy! "seanrigby" wrote: 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 ---------------------------------------------------------------- Sorry if this seems confusing. I don't know how else to state it. Thanks for your time and help! -- seanrigby ------------------------------------------------------------------------ seanrigby's Profile: http://www.excelforum.com/member.php...o&userid=34133 View this thread: http://www.excelforum.com/showthread...hreadid=555242 |
| All times are GMT +1. The time now is 11:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com