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.
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‎
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.
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)
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.
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.
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.
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().
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
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.
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
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
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.
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.
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.
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
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:
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.
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:
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?
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]
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.
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.
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.
