Sum Year To Date Forecast Based On Row And Date Criteria?
Feb 25, 2014
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
I am trying to do a sumif off all dates that fall into a specific year. I know I can do it by either adding a column in either of the sources to get the translated data but I was wondering if I can get this to work by it recognizing the format within the formula only.
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.
Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...
is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?
The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
My spreadsheet uses alternating columns for actual and budget inputs I would like to preload the budget for the whole year But the ytd formula should only show results to match the input of actuals
In otherwords, If I input actuals for period 1, the YTD formulas for actuals and budget should return YTD period 1 only and ignore the remainder of the budget inputs
After period 2 actual inputs, the formulas shlould update to reflect period 2 YTD and so on and so forth
How can I accomplich this? Sample of layout attached Totals at Cols AK-AM
I have project start date in cell C2((MMDDYYYY format).In cell D2 I have put the total days needed to complete the project.In cell E2:E6 I have got the scheduled Holidays.
I need to calculate the project completion date in F2.We work from Monday to Saturday,Sunday being off day.
I have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
I have a document that I downloaded some data from the Internet "Kronos" and within this data there is a column for date. This column is formatted as Date (01/08/13), but it only displays the date as Sun 1/01. It's missing the year, which it should be 01/01/12. I have tried to use TEXT function to convert it to month name and day name using TEXT(N4, "mmm") and Text(N4,"ddd"), but I still getting Sun 1/01. I have also tried to use Text to columns to split this date but, it does not work. Is there any other way I can convert this date into m/d/yy?
=> 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
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I wanted to solve a problem in excel. For a particular period there will be flow of units on a daily basis, which can be more or less than actual capacity. Flow received will be done on first in first out basis. I wanted to know the completion date for the same in excel. I'm attaching the sample sheet of the same.
i'm trying to write a summary page for the data collect on the first sheet and would like to find a way of counting the number of records within certain date criteria (those that are more than 30 days old). now the crucial part is that the records have a second set of critical data, their Status, and i need to find only the records that have a particular status (in this example that is Pending and Open).
if you look at the second sheet you'll see that i can find records that are associated with a specific date, but not with a range of dates; which is particularly annoying because DCOUNT can find ranges of information when using other types of number.
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table ABCStyleColorDelivery Date1ABCRed1/1/20122ABC Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows: StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
I am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function.
Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.
I found a formula and tweaked it but still cant get to work.
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.