I have excel data like from b3 to b35 there are different dates with different months and years in long date format and from G3 to G35 expenses in accounting format. what i need is the ONLY last month expense in another worksheet.
View 1 RepliesI have dates of the record add date to a database, and balance date cycle month.
What i require is loop through each record and return the next month end date of the balance date cycle month of the add date for each record. eg.
added to DB - balance date cycle month - Next balance Date
05 March 2004 - April - 30 April 2004
18 April 2003 - March - 31 March 2004
Refer sample file attached. I have populated the Next Balance date with the result required.
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
View 9 Replies View RelatedI am trying to create a run balance sheet (see attached sheet). Column C has a list of job numbers, with column D showing the reqd quantities. Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H. My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.
Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6.
When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value.
I have tried VLOOKUP and INDEX, but cannot get the thing to return.
need a formula that will give me a balance at the end, needs 2 columns with charges and credits and it allways give correct balance total on last column. have not used Excel for long time and forgot.
View 5 Replies View RelatedSee the attached spreadsheet with a macro that gets the querytable in Sheet1. In sheet2 there is a formula to get one of the balances from the querytable result. Is there a way to find the balance on sheet2 from the querytable at runtime so that I do not have to download it to Sheet1? Then I could do away with Sheet1 altogether.
View 2 Replies View RelatedIn attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View Relatedam working on a time sheet project. i'm now working on the monthly holiday planner.
Am looking for a way for a formlua to look at 2 rows and return the first of the month i.e 01-01-2009
The rows which need to be looked at are the first two rows of any given month because the 1st of the month will always be in the first two weeks of a month.
my rows are
1st week to look in. b4-h5
2nd week to look in. b19-h19
Now b4 as a paste link in it ..=roster!$H$2 which is the start date of the roster
the result of the search is then put into b2 as Month only ie. January
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I am looking for a smart way to extract the values corresponding to the first and last day of a month I specify from my time series. Column B has the dates (dd/mm/yy), column A is text(B,("mmm-yy") and column E has the values I need to extract. I have a column with all the months of the year: Dec 12, Jan 13, Feb 13 etc... and I need to pick the value in E that corresponds to the last observable day of each month and divide that by the value corresponding to the first observable day of the month.
[URL]
I need a macro that allows a user to input a Month and a Year on an input box. When this is done the macro would pull out the date for each Monday (in the format DD-MM-YY) in that month and paste it into four or five cells (A1:A5)
Eg for April 2014
A1
A2
A3
A4
A5
07-04-14
14-04-14
21-04-14
28-04-14
I want to put "Jan" (as in January) in a cell on Sheet1 and in cell A1. In cell A2 I want to dispay the start of the month and in cell A3 the end. So if I change A1 to "Feb" or any other month it will display the correct start and end dates.
I then want to use either the month or the dates to do a SUMIF statement. It a simple one like -
=IF(C5="","",SUMIF(April!R:R,"="&C5,April!G:G))
But I don't want April in the formula as I just want to update the cell in A1 to read in the data for a different month. BTW, April is on another sheet along with a sheet for each month.
I have two columns: 1) Last Date of each month from 2005 and 2) Value.
Date format: dd.mm.yy
For example:
31.01.05 1.7
28.02.05 -0.7
31.03.05 2.6
30.04.05 0.4
....
How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
Please tell me how to find the birthdays in the current month.I m uploading the worksheet for reference.I want to know the formula in this context.
View 9 Replies View Related I've been using the following code to go the current date on a spreadsheet. I now have to add the current date somewhere else on the sheet How do I modify the following code to only have it look in A:A ? Date is formatted -- m/d/yyyy (1/2/2013)
HTML Code:
Sub gotodateday()
Dim C As Range
Set C = Cells.Find(Date)
If Not C Is Nothing Then C.Select
End Sub
If I name ranges as Months, (Example: January_13. -- I can use the month without the year if it makes a difference),. How would I change it to look for the current month in A:A.
I'm working on more code to take data from hotel reports, compile totals and transfer data to several other workbooks. In prior instances, I'd been working with data that had been converted from Adobe .pdf format, so while the cell values appeared to be dates, they actually were text. I'd come up with a solution to find the first of each month in a column of dates and insert a row at that point. However, I'm now working on this with a system that actually outputs in Excel format. As such, the date column is actually normal date serial value, rather than text and my code doesn't work. What can I do to modify this snippet of code to find the first of each month and insert a row when the values in the column are actual date serials?
Here's the current code:
With Sheets("Sheet1")
For RowToInsert = .UsedRange.Rows.Count To 14 Step -1
If (Range("A" & RowToInsert) Like "*/01/*") Then Rows(RowToInsert).EntireRow.Insert
If (Range("A" & RowToInsert) Like TodayVisualMatrix) Then
Rows(RowToInsert).EntireRow.Insert
Range("A" & RowToInsert).Value = "Subtotal"
End If
Next RowToInsert
End With
Row 3 doesn't find anything because while they appear in that format, the value of the cell isn't */01/*....
i have a table with a row of dates along the top. beneath each date is a value.
the dates and values in these cells changes based on info in other sheets.
i need a way of finding the first date in the current month and returning the corresponding value.
I have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.
So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W
I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.
Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.
Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.
In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)
The Week ( Monday to sunday) which need to be calculated is the week no. in the given month
like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april
Can Anybody help me with a function that will arange all days from January acorrdingly with the year 2010. I am attacing a sample file to make it clear. I need the function to Check which month is in Cell H1 and arrange the days accordingly to the month and the year.
In my example the code must change the Cell values Like this: ...
I've read several threads about using Median(If, and I have success with one criteria, but not with three. Here's the setup:
I have a log of phone calls, and I want to find the median call length for calls in a certain month or set of months (i.e. a quarter, three months). In Calls!B I have the date of the call, in Calls!C I have the call length in minutes (i.e. "34"), and in Calls!A I'm using Concatenate and Text to return month/year (i.e. "Aug06").
With one criteria, it calculates the median correctly. With three, it only returns #NUM. Yes, I hold control+shift and hit enter. Here's my formula:
=MEDIAN(IF(Calls!$A$2:$A$500=Scores!B8,IF(Calls!$A$2:$A$500=Scores!C8,IF(Calls!$A$2:$A$500=Scores!D8,Calls!$C$2:$C$500)))))
Where Scores!B8,C8,D8 hold "Oct06" "Nov06" and "Dec06" respectively. What am I doing wrong? Is there another approach I should use instead?
I have a running balance that works beautifully in my spreadsheet, but I cannot get it to display my balance on a line above my spreadsheet. I have attached a copy of my spreadsheet to make understanding my question easier. I have a sell price that will remain the same and as the bill is paid the balance should work itself down to $0. Currently I have three deductions in my spreadsheet but the balance only shows the first deduction.
View 4 Replies View RelatedHow to filter Dr and Cr Balance from attached sheet.
FILTER.xls
See attached workbook which is a stock order workbook with a summary re-order sheet -
The problem I have is that as the running balance effects the re-order column the summary re-order sheet will re-order from a the date that stock is needed onwards until someone types in stock recieved to get the balance right.
I need another condition within the summary sheet sumproduct formula so that the order will only go through once and not be repeated until a new figure is added into the number issued column on a future date.
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month
the amount should then each month be substracted from the new month and so on, until the amount is 0
can this be done?
the second page in the spreadsheet has a post with monthly pays to the bank ...
I have to balance workload equally for everyone (Name: abc, xyz, mno) and New Orders must be shared or distributed equally for every person. New Orders must be shared in such a way that every person's percentage share must be made equal by distributing or sharing New Orders. Find an attachment named issue.xls
View 2 Replies View RelatedI am using excel 2007. I am working on an account register. I am using my spreadsheet to track when deposits and withdraws are made, when bills are paid, and when check have cleared my bank. I am using condintional formating to black out rows when bills have been paid, checks have cleare, and when withdraws have been made.
I only have one issue. I am tracking my current balance at the bottom of my spreadsheet I want to be able to go back and delete all of blacked out rows once a week without losing the value that was contained within them thus leaving the current balance unchanged.
i.e
On Monday the 17 check number 2207 clears my bank so in comumn “C” enter the word “cleared” in row 210 the row then turns black and everything is fine. My current balance now is $1,678.25, at the end of the month I want to be able to go back and delete all of the blacked out rows. So I go to row 210 and remove the row but now my balance changes back to what it was before that value was entered.
I want to know is there is a way to leave the balance unchanged even after I have deleted that row?
I need to sum the values of a column based on multiple criteria. i.e. I would like to sum the column "End Balance" based on both "Company"='B' and the "GL CODE" > 4404...... and < 4406.....
View 2 Replies View RelatedI would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
Ref attached sheet
I m facing a big and urgent problem in my worksheet..i have done my all work here but now i knew that there is a difference in this vb code..
I need your urgent favour to rectify it for me..
What i need is sheet1 is my journal jounal (data) and sheet2 is my reporting place where i set a macro button at top to update the report..
But in sheet2 column I i need an auto Balance of debit & credit..