YEAR Function In IF Formula

Aug 3, 2006

I would like to have an IF statement which if true gives a result of a date plus 365 days but if false gives a prescribed day and month but uses a year from another cell.

I was trying something like this : IF(J5="y",E5+365,01/04/YEAR(E5)) Where E5 gives a start date of a project in a dd/mm/yy format and I would like this to give the same dd/mm/yy format.

Does anyone have any suggestions - the first part of the statement should be fine it is really the year element that I am having trouble with.

View 3 Replies

Formula To Compare Previous Year To Current Year

Dec 8, 2013

Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.

Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510

Sample file is attached : Comparison_Years.2011.xls‎

View 4 Replies View Related

Function For Last Year Of Production

Feb 11, 2009

I have two rows with years and production like below:

2009 2010 2011 2012 2013 2014
4 4 4 4 0 0

I want to find last year with production which is 2012. Do any of you know about any function I can use for this?

View 6 Replies View Related

How To Sumif With Year Function

Feb 27, 2012

How do I sumif on the amount in column A when the year in column B is 2010?

View 5 Replies View Related

Countifs With Year Function?

May 27, 2014

I am trying to use a countifs function that counts all the instances where the year of the date is 2014. The dates are in date format like 3/13/2013, and contained in cells a2:a12

Here is my current formula which is not working out for me.

=countifs(year(A2:A12),2014)

View 3 Replies View Related

Financial Year Function

May 31, 2009

I have created a monster formula that extracts the Australian financial year (FYR) from a date string and would like to convert the formula to a nice tidy function.

the formula works on a date string with the following format: yyyy-mm-dd (2008-12-15)
for example the formula result for 2008-12-15 is 08/09 (yy/yy)

=IF(OR(LEFT(ExDate,1)+13,MID(ExDate,5,1)"-",MID(ExDate,8,1)"-",LEN(ExDate)10),"Change the expiry date format to yyyy-mm-dd",
IF(OR(MID(ExDate,3,1)+1=1,MID(ExDate,3,2)+1=11),
IF((MID(ExDate,6,2)+1)

View 9 Replies View Related

Function To Display First Monday Of Year

Apr 30, 2009

I am trying to write a function that will display the first Monday of the year based on the user entry.

For example, if I enter;

5/1/2011, It should produce 1/3/2011...
12/31/2012, it should produce 1/2/2012..

View 4 Replies View Related

Countifs With Month And Year Function

Feb 3, 2009

I have a column titled "Start Date", "Month/Year", &"Total". I want to count the number of dates that appeared in the "start date" column that have the same month and year of the "month/year" column. That count should be in the total count. For ex, I want the first total (april 2008) to be 1, the second (may 2008) to be 1, and so on. I tried using countifs month and year of the start date is the month and year of the month/year column. Not working.

Here's my data: ....

View 9 Replies View Related

Function That Distributes Numbers Depending On The Quarter And Year?

Jun 10, 2014

I'm trying to find out a function that allows me to evenly spread the amounts given a certain start year and quarter and end year and quarter. Let's say, I have 20 dollars and the starts in 3Q/2012 and ends in 2Q/2013. Then, the money should be spread out in four quarters. If the end is in 3Q/2013, then the spread is five quarters, and the amount distribution automatically changes.

Attached is my spreadsheet.

View 4 Replies View Related

Lookup Function (view Data For Any Quarter Of Any Year)

Oct 9, 2009

I have spreadsheet that I use to display quarterly metrics. Within the workbook, there are two spreadsheets; (1) output (2) data.

Part 1: I want the ability to view data for any quarter of any year. In columns O-P, I have created a dropdown for the year I want to view and option buttons for the quarter I want to view.

The data is organized in one sheet and includes all information by quarter.

As an example, say I want to display data for District 112 and I want to display data for the 3rd Quarter of 2007. I am trying to create a formula that would look in the table as follows: (1) look for district 112 then (2) look for 2007 then (3) look for 3rd quarter then (4) look for units sold. I tried using a modified lookup function but didn�t have any luck.

Part 2: The second part of my question is similar to the first part but in this case I need to summarize data (cells $B$18:$D:$22).

As an example, say I want to display the total number of sales in the U.S. for the 3rd quarter of 2007. In this example, I would look in the table for the following: (1) look for Division then (2) look for 2007 then (3) look for 3rd quarter then (4) sum all based on criteria.

View 4 Replies View Related

How To Use Countifs Function To Compute Month Of Year From Date Value

Aug 7, 2013

I have a problem where there are 4 arguments passed to my COUNTIFS function to give me a total count i.e. "KP","James Report","Completed","Month"

Here is the Formula I used;

=COUNTIFS(Master!$B$2:$B$29,Picklist!$C$8,Master!$D$2:$D$29,B3,Master!$H$2:$H$29,Picklist!$C$2,Master!K2:K57,Picklist!E9 )

The last part of my formula is throwing an error "K2:K57"

What I should get in plain English.... "The number of 'James Report' 'Completed by 'KP' in 'August'. (Any specified month based on what comes from my "dates field" on my Master sheet.

I have got a field in my Master sheet with dates which I have transposed into the right month of the year using Month().

View 2 Replies View Related

User Defined Function To Return Fiscal Year Of A Date

May 2, 2012

I am working with a fiscal year that starts in December and ends in November. I want to make a user defined function that will return the fiscal year of a date. I've created the below code, but it returns a zero.

Function FiscalYear(DateFY)

If Month(DateFY) = 12 Then
Year (DateFY) + 1
Else
Year (DateFY)
End If
End Function

View 3 Replies View Related

Year To Date Formula

Jan 17, 2010

Trying to do a year to date formula with my numbers going across with actual,goal, percent to goal.

I want to have the ytd auto sum based on my As of date that I did a list validation for. Can't get it to work and getting very frustrated - can anyone held with this?
I attached a worksheet with the YTD listed on the far right.

View 13 Replies View Related

Formula To Return Day Of Year

Jul 15, 2008

Is there a function that will return the day of any particular year?

For example, if the date 01/01/2008 is entered, it will return 1 and if 31/12/1977 is entered, it will return 365?

View 9 Replies View Related

Formula: Day Into Month+year

Oct 22, 2008

i need to put a date into a month +year
like this:
1/06/2008jun/08

i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.

MaandTotal
jun/081jun/0810

he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun

but i just want one and that he counts all the junes together

View 9 Replies View Related

Leap Year Formula

Sep 25, 2009

I could use a little help with a leap year (they sure are great, eh?) formula.

There's a cell (A1-K1) with a year in it (2008-2018)

The part of the formula I need is something like this:

=IF(A1 has 366 days,366,365)

So I can put this at the end of my formula.

Should be simple, but my 1044-page Excel 2000 selfhelp Bible can't offer me the solution.

View 9 Replies View Related

Message To Apear It The User Selects A Year AND Month Less Than The Current Year

Sep 25, 2006

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here

View 3 Replies View Related

Formula For Month End Date That Changes Each Year

Jun 5, 2014

I have a sheet where the columns have the month end date for each date, ex January 31,2014 February 28, 2014, March 31, 2014 etc. These dates are used in another formula to compare to the current date and if the current date is past the result is different from when if the date is in the future.

I would like a formula that would update the Month end date when we enter a new year. So for example once we get to January 1 2015 my date would change to January 31,2015, February 28, 2015, March 31, 2015. this would note be dependent on the system date but on the date in another cell.

View 3 Replies View Related

Formula To Sum By Month And Financial Year

Jun 28, 2014

I have a spreadsheet that consists of date, product number with amount received against each date.

What I need are formulas that will automatically provide me with:

1. The sub-total monthly income for each product against each financial year.

2. Total income for each product by financial year.

3. Total income for each financial year.

View 11 Replies View Related

Formula For Counting Back A Year?

Jan 17, 2012

know if there is a formula for counting back a year from a date?

Basically what I have is we operate a rolling absence/ sick year and so what happens is on a staff members return from absence/ sick we look back exactly one calendar year from the last day of absence and count the number of days off absent in that period for comparison against their paid allowance.

I have posted a mock up sheet and what I am looking for is a formula for column C. The highlighted cells are the period it would be if today was the last day of absence.

Sheet1 ABC1Days AbsentDateRolling2201/01/2010 3302/02/2010 4403/03/2010 5207/06/2010
6310/11/2010 7431/12/2010 8601/01/2011249202/02/2011
2410103/03/20112211307/06/20112112510/11/20112413201/01/20121914317/01/201216

View 5 Replies View Related

Formula That Returns Most Recent Date For Particular Year

Dec 9, 2013

The data in my spreadsheet will have a column of dates that will change depending on info input in another cell.

What I need is a formula that will give me the most recent date for lets say 2012

04/01/13
03/01/13
02/01/13
01/01/13
12/27/12
12/25/12
12/12/12
12/02/12

if use the max function I get the 4/1/13 result, what I need is a formula that will return the 12/7/12 number.

View 5 Replies View Related

Formula For Calculating Partial Year Interest

Feb 4, 2014

I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest.

But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.

Cell C2 = Today's Date=TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2

I need the interest calculation to account for partial year accrual.

View 3 Replies View Related

Formula To Calculate Years Of Service For Next Year

Dec 13, 2012

The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2

[Code] .....

Attached File : VACATION DAYS ACCURED.xls‎

View 9 Replies View Related

Formula To Recognize Month And Current Year

Mar 23, 2009

finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:

View 3 Replies View Related

Formula To Convert Fraction Of Year To Months?

Jan 28, 2012

Inmates can apply for pre-release when they have half their minimum sentence served. For example 2 years 6 months. They would have to serve 1 year 3 months.

I use this formula

=DATE(YEAR(A1)-A5,MONTH(A1)-B5,DAY(A1)-B6)

Where A1 is half the years, B5 is half the months, and B6 is half the days. The problem is when an inmate is sentenced to a minimum of 3 years and 3 months. The latter formula will not calculate 1.5 years or 1.5 months. It rounds up. What I would like to do is covert the 1.5 years to months and the 1.5 months to days.

View 5 Replies View Related

Index (Match) Formula Needs To Look Up Month And Year

Dec 18, 2013

So I use this formula on one of my task sheets to do budgets. Now the problem is we are entering 2014...yet the formula won't go into the next year without some sort of year scan:

Code:
=INDEX('Template Sheet'!$B$1:$S$233,MATCH($A11,'Template Sheet'!$B$1:$B$233,0)+2,LOOKUP(MONTH(DATEVALUE($B$8&" 1")),MONTH('Template Sheet'!$D$1:$S$1),COLUMN('Template Sheet'!$D$1:$S$1))-1)

that is the first formula but I need it to look up the year also and everything I have tried has failed miserably. So basically when it is matching with the month I have a drop down that has Jan-December...but I need it too look to the Feb 2014 not Feb 2013...How can I add the year section?

View 9 Replies View Related

Multi Conditional Formula For Year And Month

Mar 22, 2005

I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.

************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]

View 9 Replies View Related

Formula That Averages Totals From Different Months Out Of The Year

Jan 1, 2007

I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.

I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.

View 9 Replies View Related

Calendar From Formula :: Nth Day Of Week For A Month And Year

Oct 12, 2008

I'm using this formula from

[url]

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.

I will be inserting rows between the weeks to return appts, if I can get this part working.

I could make a new tab for each month, but I thought I would give this a try....

View 9 Replies View Related

Offset SUM Formula Of Year To Date In Column/row W9

Jan 9, 2007

I have months listed in this format in columns/rows 01/01/06, 02/01/06 and so on in (J9 to L9). I have a reference cell A5 = Month in the same format. This value changes every month. I want to use the SUM Formula of YTD in column/row W9. If the month is 05/01/6 then the sum should be C9 to G9 and so on.

View 2 Replies View Related