# Formula To Show A Date Range Based On The Amount Shown In Another Cell

Sep 11, 2009

Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.

I added a new cell called Track Back on the employee search sheet.

What I want to do is only show variances for the amount of days back selected in the Track Back cell.

For example if I select the last 30 days, only the last 30 days would show up below in the sheet.

I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me

## Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell

Apr 9, 2009

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.

## Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

## Formula To Create Blank Cell Based On Dollar Amount In Another Cell?

Jan 7, 2014

Ok, basically C3 is a dollar amount. The default total for C3 is \$0.00. If C3 is \$0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.

I tried this but it's not working:

=IF(C3="0","",C3-C2)

## Formula To Show Row Information Based On Date?

Mar 12, 2012

I have a Sheet1 that contains 900 rows of information 14 columns wide (C7:P7 are headers). Column M has an important date. The information is constantly changing.

On Sheet2, I want to display the upcoming dates, in numerical order for each row that's within 5 days of today's date. After the date has passed, it no longer needs to display on Sheet2.

## Date Range (formula Show A Week Number In The 2nd Table )

May 6, 2009

I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

## Macro That Locks Down Cells Based On A What Is Shown In Another Cell

Jan 4, 2008

I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:

If F4 = CST
Then Lock down H4-S4 and AM4-AZ4

But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked

Is a password necessary for this? It is ou

I just want it to lock and unlock, not protect and unprotect.

## Show Current Date Or Yersterdays Based On Time In Cell

Mar 19, 2009

I am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.

## How To Get Invoice Amount With Corresponding Date Range

Feb 26, 2014

I have a data set that has invoice amounts with corresponding bill date range. I need to get those invoice amount auto pop up in correspond month columns.

Invoice Number Bill From/Bill To Invoice Amount Dec '13 Jan '14 Feb '14
1 01/01/2014 00:00 \$100
01/02/2014 00:00

2 12/30/2013 00:00 \$200
12/30/2014 00:00

3 01/01/2014 00:00 \$300
01/02/2014 00:00

## Input Date In A Cell Will Show Month And Year Based In Database From MS Access

Jun 1, 2014

I have my ms access and ms excel which is connected each other .

=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)

YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014

=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)

HERE:

A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014

## Formula That Will Recognize Date Range And Sum Specified Field Based On Range?

Jan 2, 2014

I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.

## Counting Amount Incorrect Within A Date Range

Jun 8, 2009

I would like some help from someone on this subject if possible. I am running a stats report in a workbook where each member of staff have their own running totals of how much work they do and how much of that work is incorrect (per week). I have worked out that if I use the following:-

=SUMPRODUCT(('Barry Brooks'!\$A\$1:\$A\$2000>=\$B\$95)*('Barry Brooks'!\$A\$1:\$A\$2000<=\$C\$95))

I get the total amount of work. To explain the above, it returns the amount of times a date appears between two dates i.e B95 is week commencing date and C95 is weekending; therefore giving the total amount of work in that period.

What I am struggling with is that I need a formula to look at the above date range and then look at another column that has either a ‘Y’ or ‘N’ (for yes or no) and for the formula to count how many N’s there are (thus how many in that given range there were)

It appears to me quite a small ask for Excel but I can’t seem to get it to work.

## Calculating Amount Owed Based On Current Date

Sep 1, 2013

I'm having trouble with a complex Excel formula. I'm calculating child support owed based on the current date coupled with future child support owed. My columns are as follows:

Debt / Future Amount Owed / Monthly Payment / Total Amount Paid
10,148.72 / 83312 / 508 / 1000

On the first of each new month, the Future Amount Owed should decrease by 508. Altering the Total Amount Paid should decrease the Debt column. Progressing another month without altering the Total Amount Paid column should add 508 to the Debt column while subtracting 508 from the Future Amount Owed column.

## Count The Amount Of Entries Based On The Date In A Column

Jan 17, 2009

I have a spreadsheet containing 10,000 + entries.

Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).

Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..

## Show/Hide Worksheet Command Button Based On Cell Value Being In Another Range

Apr 8, 2008

i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.

## Formula To Show The Value Of One Cell Based On The Value Of Another To Automate Some Internal Processes

Nov 3, 2008

I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading.

What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5. I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?

## Split Equal Amount Across Months Based On Start And End Date?

Feb 6, 2014

I have contarct for amount \$5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie \$1666.66/per month.

## FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS

May 17, 2009

i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.

## Calculate Total Amount Of Kilos For A Specific Date With Given Time Range

Mar 26, 2014

I am trying to calculate the total amount of kilos for a specific date with a given time range.

As well as the average time they have been handled with in the same specifications.

Attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.

Book1.xlsx

## Formula To Apply Number Based On Date Range?

Aug 10, 2014

I am looking to get a formula based on my spreadsheet attached

I want F collumn to add 5 days onto whatever date you put in there then correspond it to the matched date period in I2-I6 then apply the pay period from H2-H6 and put it in the G Collumn next to the date that has been input?

## VBA Formula; Sum Up Part Of A Column Based On A Date Range

Feb 24, 2009

I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

## Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

## Formula To Search Text From Another Sheet And Add Sum Based On Date Range

Feb 9, 2014

I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.

Columns: Name, Size, Date
nmlclex06114/12/2013
nmlclap121012/12/2013
nmlclex0651/10/2013
nmlclex0619/10/2013
nmlclex0611/01/2014
nmlclap12124/10/2013
nmlclex06113/11/2013
nmlclap12118/12/2013
nmlclap1219/12/2013
nmlclap12130/12/2013

## Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date

Apr 21, 2006

I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.

Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!

## IF Formula - Calculate Subscription Amount Based On Capacity Of Wind Mill

Apr 5, 2014

I need to calculate the subscription amount based on the capacity of the wind mill.

The slab rates are as follows:
1. Up to 0.6 MW - Rs.3,000
2. more than 0.6 up to 3MW-Rs. 7,000
3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW
4. Above 98 MW -Rs. 1,50,000

I had tried IF statement but was not successful.

## VBA Code To Colour Certain Cells Based On RGB Colour Shown In Cell A1

Apr 17, 2014

I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.

I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.

## Cell Format: Only Numbers (no Date) With Any Amount Of Decimals

Sep 8, 2009

I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

## Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range

Jul 31, 2009

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

I'm using Excel 2007.

## How Do I Get This Amount To Show Up In The

Sep 27, 2005

I have a total amount of \$52,000 and I want to show an equal amount in twelve
columns which represent 12 months from sept 2005-sept 2006. My calendar goes
for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the
correct columns.

## Show Zero If Amount Is A Minus

Nov 19, 2008

I need a formula that that will show £0.00 if the amount is under zero, i have a formula k15/o15 that gives me a amount but if this is under zero i need this to show as zero not -£70 for example.