Calculate BiWeekly Pay Periods
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use todays date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.
I have been playing around with this formula:.....
All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......
View Complete Thread with Replies
Related Forum Messages:
Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure
The FIRST £1520 should be multiplied by 0.1 (in cell C19,)
The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)
Formula To Calculate Pay Stub
I need help to create a formula to calculate pay stub. The calculation is based on a default start date of 1/1/2006. The end date will the pay period ending in the employee paystub.We would then use the Year To Date amount to calculate the monthly salary. I need to build this into an excel formula but could not find the correct formula.
Calculate A Pay Back Period For A Certain Investment
I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.
Calculate Pay For Mileage While Factoring In The Number Of Participants
I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that).
The rest of the if statements simply follow that same formula, but for different mileage/pay rates.
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example
Hours ST OT Total Hrs Gross Pay
8 2 10 ?
i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell.
I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
stod = "night"
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
I need a formula to compute holiday pay. I am having problems when there are two holidays during the month and the hire date of the employee is after the first holiday. For instance in November, November 11 and November 26 are holidays. If the employee is hired before November 11, he gets paid for the two holidays. If he is hired after November 11, he only gets the second holiday.
A B C 1 HOLIDAYS 2 HIRE DATE RATE/ 11-Nov-09 3 HOUR 26-Nov-09 4 5 10/15/2009 60.00 6 11/16/2009 60.00 7 11/28/2009 60.00 8
Holiday Pay Calculation
I have a problem with a cell displaying the correct number of days.
We work a 38 hour week and therefore each day is 7.6hrs
I have a start date and current date and calculate the number of days employed. I know that each day employed is worth 24 minutes, so over the year it calculates to 20 days per annum.
as I have not entered all the hours taken off yet, my current holiday entitlement is 767:04 (hh:mm).
when I process this on the calculator it tells me I have nearly 101 days holiday due, but I can not get excel to display XX:XX:XX (DD:HH:MM)
What would like to know is how many days that equates to using the 7.6 hour days
Trucking Company Pay
Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes.
@ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load L and then a Flat Fee of X, And Off course The weight is not of any Issue for us for time being.
@ first 100 Meter + Loading A Value of 1 * X
@ Next 500 Meter a value of Max. 500-100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L
And again L is not important and for time Being Could left Out.
@ Next 10 Kilometer A value of [Number of Kilometer - .5]*Z Then Max would be 9.5 * Z
@ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer 10] * W Then a Traveling of 27 would be 17 * W
@ Over 30 Kilometer a Value of [Number of Kilometer 30] * V
And Finally all these would be added up accordingly.
So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup.
But Here it gets Complicated, They have these Following additional Conditions;
1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77
2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9
3; If the Road is a Dirt Road then all above would be Multiply by 1
For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road.
1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)
Filtering For Multiple Pay Types
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
Payroll Extract To Work Only On Certain Pay Period Dates.
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form.
What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period.
The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end.
What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.
Worked Hours Between Two Periods
I'm trying to calculate the total hours worked for two given periods over a shift , which can span two consecutive days ie. start 15:45 and finish at 00:15 the next day. Hours worked between 6am and 6pm are paid at standard rate, whilst hours worked between 6pm and 6am attract penalty rates. Hours are cacluated in 24hr time
I have attached a copy of the timesheet that we use so you can see exactly what I'm trying to achieve, and included most of the shifts that we have.
Allocating A Value To Different Time Periods
I got the following table.
What formula could I use so that the amount under STP premium would display at the right cell under different time periods depending on the time of the trade date, just like the following images?
Delete Periods From String
I am having trouble writing a Macro to delete multiple periods at the end of each cell. Each cell looks simiar to this: Blah Blah Blah................... I am using the InStr function to find the first period but it returns a 0 everytime. Here is the code I used to find the period. I want it to delete everything after the first period and store the value back in the same cell. Right now it just stores blanks.
Dim NewContract As String, Contract As String
Application.Calculation = xlManual
i = 2
Sheets("Product Listing by Contract").Select
Contract = Range("A" & i).Value
Do Until Contract = Empty
NewContract = Left(Contract, InStr(Contract, "."))
Range("A" & i).Value = NewContract
i = i + 1
Contract = Range("A" & i).Value
Application.Calculation = xlAutomatic
Pull Values Between Two Periods
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.
1 oakland.california.usa california
Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.
Dates Distribution Between Time Periods
What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07:
and the amounts need to be distributed in columns titled:
Within 1 Yr (From 7/1/07 to 6/30/08)
After 1 to 5 Yrs (From 7/1/08 to 6/30/13)
After 5 yrs (After 7/1/13)
Delete Leading And Trailing Periods
I have a spreadsheet with numbers of various lengths.
Sometimes they are preceded with a period and have one at the end as well.
Examples: of their present state
Examples: of desired state
Is their a way I can create a new column and insert a formula, then drag it down so that it will delete the first or last character, if the character is a period?
Count Periods Where Threshold Is Exceeded
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.
Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.
Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.
The data is formatted as follows: ...
Extrapolating Revenue Over Various Time Periods
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months.
Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day.
For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on...
Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
Subtract Times With Different Counting Periods
In the same period of time I have two countings of different stopped time.
range (A3:A23), = product code.
range (H3:H23), = time used.
with this formula I take the time used for each product. "= SUMIF($A$3:$A$23,M2,$H$3:$H$23)"
On this one I record all different lost times in the same period of time.
What I want is:
Take out from the result obtained with the above formula, the time down from SheetB.
Ex:if I used on sheetA between 7:00 and 18:00, 4:10 of the product A, I want to take to this value the downtime in the same period from sheetB.
Counting Occurrence In Date Periods
I want to count the number of sales in three periods. prior 7 to 12 months, prior 4 to 6 months, and over the last 3 months.
I have three letters that occur in column B of sheet 2.
A for active, P for pending, and S for sold.
The date of activity appears in column C for each event. It is in mm/dd/yyyy format. Currently I have over 5000 rows.
I would like to total the sales for each period and place it on sheet 1.
can you help or point me to the right place to read up on it. I can get the information by using a pivot table but there has to be a faster way.
2 Series Chart For Different Time Periods
I am trying to plot two data series in one chart. The first series has monthly corn prices for last 10 years (~130 data points). The second series has bimonthly chicken prices over the last 10 years ( ~72 points). How can I plot them together?
The bi-monthly periods for the second series do not exactly coincide with calender months ( e.g. Aug 19 to Oct 13, 2007). As a result, I can not adjust the points to match with calender months.
Match IDs Across Spreadsheets By Corresponding Time Periods
There are 2 seperate spreadsheets
S1 - "End"
No. | ID | Name | Login | Total | Jan-07 | Feb-07 | March-07 | => | Dec-07
S2 - "Datasource"
ID | Irrelevant Name | Period | State | Irrelevant Data
I need something to go through the unique IDs in the "Datasource" & find a match in the "End" spreadsheet & then insert the number "1" in the corresponding time period (matching Period with Jan-07 or which ever it's suppose to represent). The Period is written in the following format:
122007 for December 2007
12008 for January 2008
One thing, the datasource has dates for both 2007 & 2008. The end spreadsheet has 2007 in one worksheet & 2008 in another.
Convert Date Separators From Periods To Slashes
I have spreadsheets with a lot of dates where the date separators used are periods . instead of slashes /. For example, 12.02.2008 is what shows in a cell. Where I live (Australia) that means 12 Jan 2008. My need is to have Vba code that converts these to dates; e.g. 12/02/2008 or 12-Jan-2008, etc. I can manually select the cell or cells, press Ctrl+H, replace the period characters with slash characters and it works correctly - I get 12/02/2008 which I can then manipulate as a date. However, if I record a macro of the above actions, when I run the code the result I get in the cell is 2/12/2008 i.e. 2 Dec 2008. The code recorded is:
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
The only time the code gives the correct result is when the month is greater than 12; e.g. 23.02.2008 is converted to 23 Feb 2008. My computer is set up with regional settings to Australia and date format day/month/year.
Calculating Number Of Days For Job Length Periods
I'm trying to calculate the length of a work order to develop an average and future proposal estimates.
Job received = A1
In work = B1
Job completed = C1
Total days to complete = D1
I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.
If(C1=">=0" then (A1-Today()) else C1-A1)
I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job.
Find The Maximun Number For The Past N Periods In A Range Of Cells
(1) I want to find the maximun number for the past n periods in a range of cells, but I want to be able to vary n by changing the number of periods in one cell at the top of the sheet, i.e. if i enter 10 in cell A1 the function will return the max number for 10 periods over a certein range, if I change the figure to 20, then the max number for 20 periods will be returned!
(2) I have a column (lets say column c) which will return a number of values if my criteria are met and 0 if not, I want a function in another column which will replace the 0 with the next non 0 figure in column c, the gaps between the non 0 figures can vary. I feel it involves looping in some form, and writing a new function, but I am not sure.
Convert Imported Dates & Add Periods Based On Conditions
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
Count Number Of Full Time Periods Within A Time Period
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..
-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible
-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list
-- How can i make the result visible in kind of list ?
I would be nice if somebody knows a good solution..
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.
Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this
I have a spread sheet that is used to review calls placed by a call center.
Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.
We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?
My initial thought was something like this:
In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
I'm trying to create a quote... basically I need to find out about calculating ratios.
We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.
I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list.
how can i calculate the variance?
i have the upper limit (=MIN(1,(mean+half width))
i have the lower limit (=MAX(0,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
i have the half width (t value * SQRT of Var/N)
i just don't know how to get the VAR/N
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell.
Ive tried using the 'count' and 'count if' but both return values of zero.
There are words in the cell, rather than numbers.
I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.
How To Calculate Age
to calculate the age from the format date of birth shown below.
SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2-D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is -4 hours.
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 1-5 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 1-5 and a winner whenever you draw)
The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations.
To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities:
First - Second
1-W (1 winning combination)
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.
Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.
Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.
Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.
The first column is the Total Payment and the second is Inducements.
Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.
The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.