| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating time from a specific paycheck period
Hello
I have excel 2003 installed and am looking for a solution to this problem I have pay periods of Weekly BiWeekly Bi Monthly and Monthly in cell A2 and they are selected by a list by data validation with a pull down. I have in A10 a date that the payroll first begins for this person A11 and on down are being listed as the next payday. My question is: A11 and down I want to have a formula that will say ifA2=biweekly, a10+14, ifA2=weekly,A10+7 but I don't know how to make the formula in the if statemnt if A2 = bi monthly or monthly and have it come out exactly in the middle of the month or a t the beginning of the month. If bimonthly then should select 15 or thereabouts if monthly jan, feb, march on the first. Any help would be appreciated Thanks BOB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating time from a specific paycheck period
Sandy,
Sorry I took so long in getting back, computer issues... Works great for Weekly and Bi-Weekly Is there a way for the Bi-Monthly and Monthly to not select a friday and merely return a data that is in the middle of the month for Bi-Montly or the first of the month for Monthly?? The only two paydays on Friday would be the Weekly and Bi-weekly. Thanks so much in advance. Bob "Sandy Mann" wrote in message ... See If this works for you. With the list for Data Validation in H1:H4, enter in B2: =MATCH(A2,H1:H4,0) With the pay period (presumably a Monday), in A10 enter in A11 and copy down the formula: =IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,15)-WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,15),2)+5) This should return the following Friday for weekly or the Friday after that for bi-weekly and the Friday nearest to the 15th of the month for monthly and bi-monthly. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "BobR" wrote in message .. . Hello I have excel 2003 installed and am looking for a solution to this problem I have pay periods of Weekly BiWeekly Bi Monthly and Monthly in cell A2 and they are selected by a list by data validation with a pull down. I have in A10 a date that the payroll first begins for this person A11 and on down are being listed as the next payday. My question is: A11 and down I want to have a formula that will say ifA2=biweekly, a10+14, ifA2=weekly,A10+7 but I don't know how to make the formula in the if statemnt if A2 = bi monthly or monthly and have it come out exactly in the middle of the month or a t the beginning of the month. If bimonthly then should select 15 or thereabouts if monthly jan, feb, march on the first. Any help would be appreciated Thanks BOB |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating time from a specific paycheck period
Hi Bob,
If by the middle of the month you mean the 15th then try: =IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1)) The 15th may fall on a weekend so if you want a Saturday to show the previous Friday and Sinday to show the following Monday try: =IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1)-(WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,14*(B2=4)+1))=7)+(WEEKDAY(DATE(YEAR(A10),MONTH(A 10)+$B$2-2,14*(B2=4)+1))=1)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "BobR" wrote in message ... Sandy, Sorry I took so long in getting back, computer issues... Works great for Weekly and Bi-Weekly Is there a way for the Bi-Monthly and Monthly to not select a friday and merely return a data that is in the middle of the month for Bi-Montly or the first of the month for Monthly?? The only two paydays on Friday would be the Weekly and Bi-weekly. Thanks so much in advance. Bob "Sandy Mann" wrote in message ... See If this works for you. With the list for Data Validation in H1:H4, enter in B2: =MATCH(A2,H1:H4,0) With the pay period (presumably a Monday), in A10 enter in A11 and copy down the formula: =IF($B$2<3,A10-WEEKDAY(A10,2)-2+$B$2*7,DATE(YEAR(A10),MONTH(A10)+$B$2-2,15)-WEEKDAY(DATE(YEAR(A10),MONTH(A10)+$B$2-2,15),2)+5) This should return the following Friday for weekly or the Friday after that for bi-weekly and the Friday nearest to the 15th of the month for monthly and bi-monthly. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "BobR" wrote in message .. . Hello I have excel 2003 installed and am looking for a solution to this problem I have pay periods of Weekly BiWeekly Bi Monthly and Monthly in cell A2 and they are selected by a list by data validation with a pull down. I have in A10 a date that the payroll first begins for this person A11 and on down are being listed as the next payday. My question is: A11 and down I want to have a formula that will say ifA2=biweekly, a10+14, ifA2=weekly,A10+7 but I don't know how to make the formula in the if statemnt if A2 = bi monthly or monthly and have it come out exactly in the middle of the month or a t the beginning of the month. If bimonthly then should select 15 or thereabouts if monthly jan, feb, march on the first. Any help would be appreciated Thanks BOB |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need help calculating revenue over period of time | Excel Discussion (Misc queries) | |||
| Calculating interest for loans that do not run the entire period | Excel Discussion (Misc queries) | |||
| Query Items Listed During a Specific Time Period | Excel Discussion (Misc queries) | |||
| Calculating Paycheck to Include Overtime | Excel Worksheet Functions | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Linear Mode
