# 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).

ADVERTISEMENT

## Text Based On Date Being Year From Current Date

Apr 29, 2008

I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!

Cell A1 contains a Date eg (01/01/07)

In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.

## SUMIF With Year As Criteria And Date As Range?

Apr 2, 2014

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.

## 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.

## Return A Date Based On Year / Month

Jan 21, 2009

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.

## Sum Year To Date Based On Most Recent Input

Oct 7, 2006

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

## How To Forecast Project Completion Date

Sep 28, 2008

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.

The detail is as follows:-

## Shade Cell Certain Color Based On 1 Date Every Year?

Aug 6, 2013

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...

## Subtract 1 Year From Date Based On Month (January)

Jan 4, 2014

How I can subtract a year when I formulate based on if the current month is January.

Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.

I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.

## Date From Internet Does Not Display Year And Can't Be Converted To Date

Jan 8, 2013

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?

## Date Formula: Date Another Cell Rounds Up To Jan 1st Of The Next Year

Oct 22, 2007

what formula could I use when I type in a date another cell rounds up to Jan 1st of the next year? ie 2/1/07 & in another cell it returns 1/1/08.

## 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

## Sum Year To Date Based On Month Chosen, Rank Values & Compare Rankings

Dec 22, 2008

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).

## Date To Show Only Date Without Month Or Year

Jan 9, 2013

I have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.

## Date And Month From A Column And Year Should Take Current Year

May 14, 2009

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)

## Look Up The Date To See Which Year It Falls In And Return The Year

Oct 13, 2008

i have the following table of information

Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93

and a list of dates i need to look up the date to see which year it falls in and return the year

## Convert Date From Any Year To Current Year

Feb 27, 2009

DATE function won't return TODAY()'s year in the "year" slot.

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

## Calculate Next Due Date Based On Start Date Frequency And Current Date?

Mar 9, 2014

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)

## Year Month Date To Month Date Year Code

Jul 28, 2009

Serial No Search  E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4

E - Year Month Date
I need F column as Month Date Year Format

## Add To Date Based On Criteria

Nov 24, 2006

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.

## Using DCOUNT And Date Based Criteria

May 15, 2009

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.

## Find Max Date Based On Criteria

Mar 20, 2012

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.

## MAX Date Based On Specific Criteria

Jul 16, 2007

I need to determine the MAX date in a column based on specific criteria. Column A6:A500 = Task Names, Column N6:N500 = Dates (there are some blanks).

What I need is to determine the latest date in column N6:N500 based on which task I choose from column A6:A500.

## Add To Current Date Based On Criteria

Jan 3, 2007

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? ....

## Find Earliest Date Based On Certain Criteria

Jan 25, 2010

Ok so let me set this up. I have 2 columns: .....

## Count W/multiple Criteria, Based On Date

Dec 2, 2008

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

## Extracting Rows Based On Date Criteria

May 7, 2008

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?

## Return Latest Date Based On Matching Criteria

Jul 25, 2014

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.

=MAX(INDEX(\$A\$1:\$C\$40,MATCH(A2,\$A\$1:\$C\$40,0),3),1)

MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14

## Index Match Based On Date Range And Name Criteria?

Jan 1, 2014

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.

## Deleting And Adding Rows Based On Date Criteria

May 21, 2006

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.

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