Selecting A Month To Calculate Invoice

Jan 16, 2008

Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates.

H I J
27 Qauntity: Cost: Amount:
28 55 R 185.00 R 10,175
29
30 Net amount: R 10,175
31 Vat: R 1,424.50
32 Amount Due: R 11,599.50

Invoice
[Table-It] version 09 by Erik Van Geit


RANGE FORMULA (1st cell)
H28 =SUM(Divenic!G5:G5004)
J28 =SUM(H28*I28)
J30 =J28
J31 =J30*0.14
J32 =J30+J31

[Table-It] version 09 by Erik Van Geit

View 9 Replies


ADVERTISEMENT

How To Indicate Which Month Adjustment Will Posted To Invoice

Feb 15, 2012

I'm trying to determine how to indicate which month an adjustment will post to an invoice.

Column A= billing cycle date
Column B= Market
Column C= Adjustment Approved Date
Column D= Adjustment Amount
Column E = Which invoice will credit post to:

So I'm trying to build a formula in Column E that will look at the cycle date in Column A compared to the Adjustment approved date in Column C and then kick out which invoice the adjustment will appear on. The values in Column E were placed mannually to show what I'm trying to accomplish. if the adjustment approved date is = to a cycle date it will show up on the same invoice. ie if approved on the 1st and the cycle date is the 1st the invoice will reflect the approved adjustment.

ABCDE1Cycle Day of MonthSales MarketAdjustment Approved DateAdjustment Amountposted invoice21Salt Lake12/15/2011-$1,300.00Jan '1232Denver12/22/2011-$3,802.01Jan '12411Atlanta1/12/2012-$5,292.00Jan '1255Dallas1/23/2012-$6,000.00Feb '12628New York2/1/2012-$5,000.00Feb '1272Denver12/5/2011-$500.00Jan '1283Seattle2/4/2012-$440.74Mar '12912San Diego1/4/2012-$500.00Jan '12101Phoenix1/17/2012-$257.87Feb '12112Denver1/18/2012-$1,220.92Feb '12123Seattle2/5/2012-$911.03Mar '12134Spokane1/30/2012-$20,391.86Feb '12145Dallas12/6/2011-$45.63Jan '12151Phoenix12/7/2011-$7,176.14Jan '12

View 2 Replies View Related

Calculate Number Of Days Between Invoice And Today Date

Feb 21, 2014

I'm trying to create a new column which calculates the number of days from the today's date to the due date of an invoice. Also a Column that an invoice is/was overdue.

Column A - Clerk inputs date that invoice was received
Column B - Clerk inputs the date that the bill was paid
Column C - Auto populates the due date of the invoice (20 days after the invoice is received)
Column D - I want a number of days to auto populate based on today's date that will show how many days we have to pay the bill. Example: today is 2/19/2014, bill is due 2/22/2014 (Column C), column D should read 3 (I would prefer is the number is black for "we have days left to pay", red for "we're behind")
*Extra bonus for Column D, if the column goes blank after a date is entered into Column B* - but not necessary

Column E - I would like if the date the bill was paid (Column B) is greater than the date the invoice is due (Column C) to show "Overdue" in the cell.

View 9 Replies View Related

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

Jan 28, 2010

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View 9 Replies View Related

Using Offset From Latest Month To Calculate 3-month Average Within A Range

Jul 1, 2009

I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........

View 9 Replies View Related

Saving An Invoice Number When Reopening An Invoice

Oct 30, 2008

I have an Excel Invoice Template, saved as a template.

I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)

After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.

What I need to know is:

Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?

View 9 Replies View Related

Autofiltering Dates By Selecting Month

May 14, 2009

I have a large spreadsheet which has a column of dates (dd-mmm-yyyy) in it. I would like to be able to filter by month regardless of dd and yyyy. I would like to see all quotes in Jun.

View 2 Replies View Related

Selecting Between Two Date Ranges (based On Days In Month)

Jul 8, 2014

I have a list of data that displays data by day, I want to select only the data for the for the current month and then the same day time frame for previous months in the data set.

E.g.

Today's date 08/07
Full days into month 7

Formula to sum data in columns B that only looks at dates 01/07-07/7

I will the adjust that formula for a January date that only pulls data for 01/01-01/07

Then repeat for Feb-June

View 3 Replies View Related

UDF To Calculate The Month

Aug 13, 2007

I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb

View 9 Replies View Related

Calculate Fiscal Month

Oct 7, 2008

I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.

View 4 Replies View Related

How To Calculate (first Of The Month After 60 Days)

Apr 17, 2013

I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.

Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?

View 8 Replies View Related

Auto-calculate Last Day For A Month

Nov 28, 2006

I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).

View 9 Replies View Related

Calculate Interest Per Month

Jan 12, 2010

Sample data:

02-06-09 to 18-06-09 earned $2500
19-06-09 to 23-08-09 earned $3500
24-08-09 to 31-12-09 earned $4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

View 9 Replies View Related

Calculate Sum Of Values Based On Month

May 21, 2014

I have been using Excel for a while but i'm kinda stuck at one point where i have to calculate the sum of the numbers corresponding to the month of the data.

Column 1 has all the dates in different months - in this workbook i've included only jan, feb and mar. The second column has the counts corresponding to the dates.

Now if a user inputs the month (1,2,3) number in F2, then we need to compute the sum of all the numbers from second column corresponding to the month in column 1 and display in cell F4.

View 3 Replies View Related

Calculate The 6th Month From Start Date?

Dec 25, 2013

I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.

ie: start date: 26/12/13 -- End date: 25/06/14

View 3 Replies View Related

Auto Calculate The Sart And End Of Each Month

Dec 9, 2008

I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.

View 4 Replies View Related

Calculate 1st Tuesday Or 3rd Friday Of Month

Nov 11, 2009

I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

View 3 Replies View Related

Calculate Date Of First Business Day Of A Month

Sep 19, 2012

I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.

For example:

I have a workbook with two worksheets in it.

The first worksheet is the input page.

On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.

Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12
Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP)
(in this example, this should be 3) and displayed as 03-Sep-12
Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1

The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.

The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.

Daily
Date
USD

29 June 2012
1.0191

1 August 2012
1.0507

[Code] ........

Worksheet 1, Cell C1 should display the value of 1.0266

View 5 Replies View Related

Calculate Third Wednesday Of Month Plus Interval

May 21, 2013

For any given year, I would like to calculate the date of the third Wednesday of each month for that year, plus the interval (in weeks) between two consecutive months (Which will be either 4 or 5).

Example:
Enter 2013 in cell A1
Output would be:
A2 - Jan 16
A3 - Feb 20
A4 - Mar 20
A5 - Apr 17
..
A13 - Dec 18

AND

B3 - 5 weeks (interval Jan-Feb)
B4 - 4 weeks (interval Feb-Mar)
B5 - 4 weeks (interval Mar-Apr)
..
B13 - 4 weeks (interval Nov-Dec)

View 2 Replies View Related

Calculate Sum Of A Column If Month And Year Is Correct

Jun 19, 2014

So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.

Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for February 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.

See attached file for example and for my =sumproduct formula.

Excelforumexample1.xlsx‎

View 5 Replies View Related

Calculate Number Of Calendar Days For Each Month?

Mar 17, 2014

I have a list of months in column A and would like a formula in column B that calculates the number of calendar days for each month (i.e. Jan=31, Feb=28 etc.)

View 9 Replies View Related

Date Function Query (calculate The Last Day Of The Month)

Nov 12, 2008

I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)

The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)

This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.

View 4 Replies View Related

Calculate Year To Date Based On Known Month

Dec 6, 2009

I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

View 3 Replies View Related

Calculate The Quartiles With The Condition Of The Year Or The Month Or Even Both

Oct 16, 2008

i have a dataset which is like a timeseries with 3 columns
first is year second month third values
so lets say like this

year month day value
2004 12 29 100
2004 12 30 200
2004 12 31 300
2005 01 01 50
2005 01 02 60
.....

I need to calculate the quartiles with the condition of the year or the month or even both... I suppose i need something like an array but i couldnt make it work untill now.

View 2 Replies View Related

Auto Calculate End Date Of Previous Month

Jan 28, 2009

I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?

I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08

I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09

View 2 Replies View Related

Excel Formula To Calculate Fiscal Month

Dec 19, 2012

I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.

I have the calendar listed on a different tab.

Fiscal Month
Start Date
End Date

January
1/1/2012
1/31/2012

[Code] .........

The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)

View 4 Replies View Related

Calculate Date Of Next Occurring 3rd Friday Of Month?

Oct 18, 2013

I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:

Code:
=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))

The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:

[URL]

View 4 Replies View Related

Formula To Calculate How Many Number Of Days In A Month

May 7, 2014

In cell A1 = 15/06/2006 (dd/mm/yyyy)

What is the formula to find how many no. of days in month provided in cell A1.

View 2 Replies View Related

Formula To Calculate Date End Of Prev Month

Feb 22, 2008

I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.

I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.

where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.

I would like a formula that will calculate these previous month end dates correctly.

See example below ....

View 9 Replies View Related

Conditional Formatting - Calculate The Numbers Of Month And The Revenue?

May 5, 2014

i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved