Tracker
HOME    TRACKER    Excel

# A Formula Which Is Calculate 4-5 Dates Formula In One Cell

## i need a formula for which calculate all cell (calculating years months and days formula). FIND ATTACHED .xls file i need a answer in cell highlited in yellow color.

Related Forum Messages:
Formula To Calculate Time Between Dates And Other Criteria
attached is an example of a timesheet we use.
now after lengthy discussions the comany say its not 'in their interest' to use acess so i'm stuck with excel on this one.

I need to sum a column on many critiera, which i feel maybe a job for the trusty old =SUMPRODUCT. But thing is i need to sum a column based on a date range, Rate, Day.

I have manually typed in the number i think it should produce but as far as formulas go

1st off need to calcualte date range, which is situated on the top of the spreadsheet.
2nd some how tell the formula that Normal overtime is either classed as rate 1 Saturdays classed as 1.5 and sundays and bank holidays classed as 2.

Conditional Formula: Calculate The Difference In A Date Between Two Dates
I'm trying to make a formula to calculate the difference in a date between two dates. For exaple, in my business we have a due day for something. So the due date could be Feb 10, we need to know if it was done on time, late, or early. So I've gotten it to display late by doing this: =IF(B2>A2, "Late", (A2-B2)) But then I can't figure out how to incorporate this: =IF(B2=A2, "On Time") or =IF(B2<A2, "Early").

I would like to displaly this all in one cell. So if the due date is Feb 10 and it's turned in Feb 8 it's early, turned in Feb 10 it says on time, turned in Feb 11 it's late. I also tried this but it didn't work: =OR(IF(B2>A2,"Late")*IF(B2=A2,"On Time")*IF(B2<A2,"Early"))

Datedif Formula: Calculate The Number Of Days Between Dates In Column A And B
I'm trying to calculate the number of days between dates in column A and B. I've looked at the examples in this site and thought I used the formula correctly, but the cell returns an error message when I type: =DATEDIF(A1,B1,"D")

Formula To Calculate Average Of Every Other Cell
I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...

Formula To Calculate Cell Location
i'm not sure how to describe this. i've never had to do this before. here is what i'm trying to do. and it's not working.

=MIN(B2:B(G2-G3))

basically instead of saying =MIN(B2:Bx)....i want the "x" to be variable based upong the G2-G3 condition. can this be done some other way?

Calculate Formula Based On Cell Criteria
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.

Formula That Will Look In Col A And Perform A Subtraction In Cell B1 Between 2 Value Dates
i need formula that will look in Col A and perform a subtraction in Cell b1 between 2 value dates 28/12/08 -12/12/08. Though I could have done this manually but was wondering if there is formula which will avoid duplicate dates. So the formula is set and when i copy new data in Col A daily the formula should be able to pick 2 dates regardless where the second date starts at...

Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places

For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.

Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.

I included an example of what i mean in this excel file which should clear up any confusion.

Calculate Event: Run Macro Whan A Specific Cell Change (which Has Sum Formula)
I have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.

Formula To Calculate Age
i am trying to build a formula to calculate a persons age.

I have tried using the DATEIF function but i keep getting a N/A

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 - How To Calculate For The 35 Min At Say 15.24
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.

for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.

the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.

so i need to know how to calculate for the 35 min at say 15.24 ph.

Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0
in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take
=A1+B1+C1:

and produce:
=Bananas+Apples+Oranges

Formula For Between Dates
I'm trying to write a simple formula that is an IF, BETWEEN, THEN statement about dates.

I want the formula to read:

If Date1 is greater than Date2 and less than Date3, then Date1.
If Date1 > Date2 < Date3, Date1

Formula To Calculate Discounts
I have attached an example ....

If both of the discounts being offered are positive then I am ok with.
In this example the first discount ofered is -25% and the second one is +50%.

If the required charge is higher than the standard charge then, in this example, the final charge should give the value as 2,500.

What would the formula be that added up the 2 discounts together and gave the correct value in cell B8?

Calculate A Baseline Formula
I am trying to calculate a baseline average sales formula skipping weeks were there was a promotion. The maximum weeks = 8. In my table, promotions are indicated by equal values in the promotion column (C) and weekly sales column (A). If there was no promotion a 0 is in column (C).

Beginning the formula in cell B10, If A10<>C10, average A10:A2
If a11 = c11, average A10:A3 ( move up a cell in Col A and average that cell with the preceeding 7 numbers).

=AVERAGE(IF(A10<>C10,A3:A10,A2:A9))

However, if another value in A equals the corresponding C values within the averaging range (i.e. A8=C8), I want to skip "A8" in the average calculation and include the next cell outside the range (A2 or A1). In other words, I want to average a maximum of 8 preceeding entries in Col A that meet the criteria A<>C.

Formula To Calculate Turnaround
I am trying to calculate the number of items that took more than 5 days to complete.

The user enters the month and the year for which stats are required in G5 and G6, respectively (e.g., February and 2008). Both cells are formatted as General.

Column D contains the start dates, while column Q contains the completed dates.

I used the following formula but I am not getting the expected result:

PHP
=SUMPRODUCT(--(DATE(YEAR(\$D\$10:\$D\$12),MONTH(\$D\$10:\$D\$12),DAY(\$D\$10:\$D\$12))>=DATE(\$G\$6,CHOOSE(MATCH(\$G\$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12),1)),--(DATE(YEAR(\$D\$10:\$D\$12),MONTH(\$D\$10:\$D\$12),DAY(\$D\$10:\$D\$12))<=DATE(\$G\$6,CHOOSE(MATCH(\$G\$5,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),1,2,3,4,5,6,7,8,9,10,11,12)+1,0)),--(\$D\$10:\$D\$12<>""),--(\$Q\$10:\$Q\$12<>""),--(DATE(YEAR(\$Q\$10:\$Q\$12),MONTH(\$Q\$10:\$Q\$12),DAY(\$Q\$10:\$Q\$12))-DATE(YEAR(\$D\$10:\$D\$12),MONTH(\$D\$10:\$D\$12),DAY(\$D\$10:\$D\$12))>5))

Formula Calculate The Percentage
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers
'chosen' at random by 500 unique people.

I have another row of numbers representing the 'frequency' that the
aforementioned number was chosen in cells A2 to Z2.

Example:

1 2 3 4 5 6 7 8 9 10 11 ...
10 4 16 9 23 20 61 13 18 43 17 ...

Is there a formula that I could use to calculate the percentage (0%-100%)
that each number was chosen?

Formula To Calculate Penalties
i am trying to make a wage sheet calculator using excel.. and the problem arised was that how to deduct late fine .. i have a sheet which says for example if he is late for 15min then 10\$ and if he is late for 30 min then 15\$ ..etc..

how to write the formular if the person of salay say 1000\$ range comes late after 15min. 30min . 60min .etc...

Calculate Formula According To Date
I am creating a proforma income statement for a business plan and need the cell values to calculate (or not) depending on the startup date that is entered on another sheet.

The following formula works if the value of Startup!\$C64 is "March", but if it's "February" it returns 0.

=IF(AND(Startup!\$C64<="March",Startup!\$D64=2008),Assumptions!\$S17*P\$3,0)

What I'm trying to do is have the cell on the income statement only perform the calculation if the startup date is earlier or the same as the month referenced in the formula.

Perhaps I can't use the "<" with text?

Formula To Calculate 2 Cells
I have conditional statement {=SUM(IF('name of sheet'!\$H\$2:\$H\$226=H8,1,0))}

When ever I try to add a 2nd condition in using the wizard, I get incorrect results.

H8 is the cell that I want the calculation based on. How do I put on there that I want the calculation based on H8 or H9. If the answer is an easy one I apologise in advance. I'm a newbie to excel.

Formula To Calculate The Value Over Limit
I need to work out a formula to calculate the “value over limit” in the following scenario:

Cell D11 contains a total balance (input manually)
Cell D1 is the total of cells E to H
Cell A1 contains the formula to calculate the % of Cell D1 against the balance in Cell D11
Cell B1 will used to input a credit limit which will either be blank or the value of the limit

In Cell C1 I need a formula that will return 0 or value over limit based on the following-

Formula to calculate the value over limit: If cell A1 >10% of Cell D11 return the value difference between 10% of Cell D11 and the value in Cell D1
example; cell D11 = £100,000 & cell D1 = £22,000 (cellA1 = 22%): 10% would be £10000 therefore, the result would be £12,000 over limit

However, the comparison also has to take account of Cell B1. If this contains a balance then this needs to be taken into account e.g. in the example shown, if cell B1 contained a balance of £5000 then the result would be £7000 over limit or if the figure was =>£12000 the result would be 0.

If the % in Cell A1 is <10% the value is always returned as zero in Cell C1 regardless of any balance in Cell B1.

Refuses To Calculate A Formula!
I have a spreadsheet that I was sent (with some resource calculations on it - nothing major or huge) and I have added a sheet to it. The issue is that, on the existing sheets, any formula I add works but on the new sheet - they don't!. I get on the new sheet is the formula written out in the cell:

I have
- looked at the formula and it is correct
- cut and paste all sheets (values only) into a new sheet
- had a look in the VBA window - nothing stood out

Formula To Calculate Best Of Three Numbers
how to calculate the best of three numbers from 5 numbers in a row?

I want to know the exact formula for this.

Formula To Calculate Row HEIGHT
I need a formula to calculate the height of a cell. Basically I will repeat this formula thoughtout the database, and each cell in column U will have its row height.

have come up with the following

Function RangeHeight(rng As Range) As Double
RangeHeight = rng.Height
End Function

Now, the problem is, that the formula does not automatically update when the row height changes, and when I open the file on another pc, all the formulas change to #NAME since it does not recognise the custom formula.

Formula: Calculate Percentage
I have in row D a list of numbers and in cell F a list of salaries. I need a forumla to tell me what percentage D1 is of F1 etc.

IE the formula will be something along the lines of D1 * 12 / F1 * 100

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.

Formula: Calculate Commision Of 10%
i need a formula to calculate commision of 10% eg. E14=\$1500. i need the answer to get 10% of that

Formula To Calculate Hours
im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable- will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga

Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B\$1:C\$2000,2,FALSE)&J6

Formula's For Calculate Sales Commissions
I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %.

AgentSales Price CostComm Pd
AS150 75
JK255 185
JD325 250
JD125 50
AS50 10
AS50 10
AS335 250
JW75 25

Conditional Formula - Calculate Only If Net Unit Is Zero
Here's the table for one game

Profit/Loss(\$)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.

## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.

Formula To Calculate Values From Words
There are five levels of Performance.

Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5)

There are ten rows of Categories of Performance.
There is one final column of Performance Summary for each category.

There are seven columns that are report sources of Performance.
There is one final row of Performance Summary for each report source.

There is one cell that is a final Summary of Performance Overall.

The cells have drop down lists of words only.

I need the Summary cells to show the result of calculation of the average for each row and column.

Formula To Calculate A Fee Based On Performance
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:

KEYPerformance
2010,000
2120,000
2230,000
2340,000

I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:

KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4

For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:

Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999

So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.

How can this be combined into a lookup/range/low-high formula to extract the correct values????

Calculate A Formula Instead Of Returning Text
I am compiling data from several different, variable, worksheets. I am using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data.

However, it is returning the text of the formula instead of
the results of the formula. How can I make it calculate the result instead of
simply displaying the formula text?

Conditional Formula And Calculate Automatically
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.

The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.

im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...

this is the formula I have now, obviously it doesnt work

=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))

Formula To Calculate Staff Performance
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this

1 - Yes
0 - No
left blank - n/a

B3 counts the number of entries in the column
B2 counts the number of 1's in the column
B28 = B2/B3

Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?

Logic Formula To Calculate Distance
I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C.

I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.

Formula To Calculate Sick Days
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.

For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.

Formula To Calculate Hours Worked
I have this spreadsheet and in it the time is changed from military time to regular and then I use a formula to calculate hours worked. On some of these the total is off by one minute. Does anyone know how to fix this?

I don't know how to paste the spreadsheet so you can see formulas,

Stopping Formula To Automatically Calculate
Is there a way in which I can get excel not to run any formulas until I run some sort of command or click a button in order to tell it to?

For example, I have two sheets, one sheet has raw data entered manually into each cell, the other sheet has a large amount of formulas to calculate totals from this raw data. But every time a number is entered into a cell in the raw data sheet Excel says 'Calculating Cells', I want to be able to enter all my raw data, then go to my totals sheet and execute all my formulas.

Formula That Would Calculate What Workday A Date Would Be
I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

Formula To Calculate Totals From Three Different Sheets
I have to calculate the totals for every individual for the past three months. I have to do this for 200 people, is there a formula i could use to do this? Eg. if a person made 50 sales in Jan, 40 sales in Feb and 100 sales in March i need a formula that calculates the grand total, which will be 190.

Formula Won't Calculate When Only Some Of The Three Variables Are Input
In column H: =IF(ISERROR(L4-J4-K4),"",IF(OR(K4="0", K4=""), L4-J4, L4-J4-K4))
So, if formula is going to come up with an error, I don't need to know. If it's not an error: L - J - K. Except that sometimes there isn't information in J or K, and the formula won't calcluate L - 0 - 0, unless I physically enter in zeros.

=IF(ISERROR(L4-J4-K4),"",IF(K4=""), L4-J4, L4-J4-K4))
and
=IF(ISERROR(L4-J4-K4),"", L4-J4-K4))

I don't want the people using this to have to enter in a plethora of zeros in order to force the calculation. What am I doing wrong? I've attached workbook with the relevant information in it. You can see in the middle that if there is nothing entered, it won't work right.

Formula To Calculate Based On Criteria
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.

The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.

I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.

Formula Won't Calculate On Specific Computer
We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:

=IF(D5="","",IF(A5="",NOW(),A5))

This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P

All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation.

Conditional Formula To Calculate Total
I need a formula to calculate a total based on two conditions.