Fiscal Year Calculation
Feb 3, 2006
I have an issue within Excel and I really hope you can assist me in cracking it. Let me try to describe my problem;
I have a given date, e.g. January 22, 2001
With this date I need to add 4 years, so I simply did cell * 1460 (as that is 4*365), so I end up with January 21, 2005. So far so good...
Now I want to know the Fiscal Year which this month is in.
Example, fiscal year 06 is from June 1, 2005 - May 31, 2006.
This means that I need some kind of calculation to see in which Fiscal Year this date is. In my above example (January 21, 2005) this is FY06, but June 1, 2006 would be FY07.
How can I do this? I experimented with using Year() and Month() and then do some logical check with IF, but I can't crack it... Who could help???
When possible the solution should not have hardcoded years, so even if I enter a date in August 2050 it should still say FY51.
View 12 Replies
ADVERTISEMENT
May 2, 2007
I have a spreadsheet full to the gills of patient information. I have a separate sheet with information from the first spreadsheet like counts of a certain type of tests or diagnosis codes using a 'countif' function. This is based on all the info in that first sheet. I want to make a second sheet with the same basic setup, but only from the patients who visited during this fiscal year. Is there a code I can use to make this happen?
Let's say the date of the visit is in column A and the test type is in column N. The code I'm already using is =COUNTIF(Info!N:N,"*TestName*") How do I get this to return results only if the date of visit was within the fiscal year?
View 9 Replies
View Related
Sep 1, 2008
writing a formula that would result in my organizations' business fiscal year.
Assuming the fiscal year is 2008, the quarters are as follows:
1st QTR = 7/2007 - 9/2007
2nd QTR = 10/2007 - 12/2007
3rd QTR = 1/2008 - 3/2008
4th QTR = 4/2008 - 6/2008
When 7/1/2007 is entered, the result should be Q1-2008
10/20/2007, Q2-2008 and so forth.
View 13 Replies
View Related
Nov 8, 2002
When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar year. Can this be done by fiscal years?? using an addin or something, where I set the start month of the custom fiscal year.
View 5 Replies
View Related
Jan 22, 2013
I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
View 6 Replies
View Related
May 6, 2009
This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.
Here is my
View 7 Replies
View Related
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
Jan 20, 2014
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
View 1 Replies
View Related
May 13, 2014
I have a Fiscal Date macro that will looks at a date to give me the current fiscal month or week, but I would also like to add the functionality for it to give me the quarter.
In my business, the fiscal year starts in October. The quarters of course are as follows:
Oct-Dec = Q1
Jan-Mar = Q2
Apr-Jun = Q3
Jul-Sep = Q4
I would like for the quarter to be formatted with the year ex. FY14Q1
Here is the Code, but I have also attached an Excel file with the .bas file.
[Code] .....
Attached File : Fiscal.xlsm‎
View 3 Replies
View Related
Oct 25, 2009
Is there an in-built function within Excel that will help me ascertain what year is next year, and what year is the year before current? I am using =YEAR(TODAY()) to ascertain what year we are currently in, but cannot figure out how to go one backwards and 1 forwards?
View 2 Replies
View Related
Mar 28, 2007
My employer uses a scoring system to give a value to an individual’s sick record in the current rolling year.
Each person, has recorded, the number of sickness occasions and the total number of days in this rolling year period, these details go onto the final sheet where a score is calculated.
At present to get an accurate period, I have needed manually to deduct days to produce the correct time frame, ie 27/3/06 to 28/3/07 or when the sikness carrys on into the folling month.
I have tried without success to modify the rolling year formula, which as it is, will include the whole of March 06.
The formula is
=SUMIF(B5:D49,">="& DATE(YEAR(TODAY()),MONTH(TODAY())-12,1),L5:L49)
View 9 Replies
View Related
Dec 3, 2008
I'm calculating sick days on an Attendance Report.
- Sick days accumulate at a rate of 1.25 per month.
- Employees will carry-forward unused sick days from 2008.
- The maximum sick days is 30.
In trying to create a formula that will multiple the current month (eg Dec=12) by 1.25, subtract any sick days taken YTD (S16), and add the carry-forward days from 2008 (W16), I noticed one problem with the following formula:
View 2 Replies
View Related
Aug 3, 2006
What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks.
I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)"
B1 is the current days date
B2 is the entered date
What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
View 3 Replies
View Related
Oct 7, 2008
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
View 4 Replies
View Related
Jul 24, 2013
I have data for 6 monthsone column has all the dates
1/1/2013
1/2//2013
1/5/2013
1/8/2013
1/10/2013
1/15/2013
1/21/2013
1/31/2013
I have for 6 months like these dates
I need to convert all these dates to week 1- week 5
week 1: 1-7, week 2: 8-14, week3: 15-21, week4: 22-28, week5: 29-31
Instead of 1/1/2013 it should show week1 instead 1/10/2013 week2 like this
View 9 Replies
View Related
Oct 24, 2009
Our fiscal calendar year is from Nov 1 (Q1) to Oct 31 (Q4). If I have a column of cells with each cell containing a date of the year, do you know of a way for me to be able to determine which quarter a particular date falls into? See the attached workbook for an example.
View 3 Replies
View Related
Nov 12, 2009
I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.
I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.
View 3 Replies
View Related
Dec 15, 2008
Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?
Start End Month
09/21/08 10/18/08 Oct
10/19/08 11/22/08 Nov
11/23/08 12/27/08 Dec
12/28/08 01/24/09 Jan
01/25/09 02/21/09 Feb
02/22/09 03/28/09 Mar
03/29/09 04/25/09 Apr
04/26/09 05/23/09 May
05/24/09 06/27/09 Jun
06/28/09 07/25/09 Jul
07/26/09 08/22/09 Aug
08/23/09 09/26/09 Sep
View 6 Replies
View Related
Oct 8, 2008
I am attempting to check to see if the current week is the end of a period or not. If it is, I need to display some text, and if it is not, I need to display nothing.
Since I am Computer Science major, the best way I can describe this is through and if structure from a low-level programming lanuage (like C++ or Java):
View 2 Replies
View Related
Dec 19, 2012
I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.
I have the calendar listed on a different tab.
Fiscal Month
Start Date
End Date
January
1/1/2012
1/31/2012
[Code] .........
The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)
View 4 Replies
View Related
Dec 2, 2012
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
WK 2 OCT FY2013 coverted to Oct 8 - 14
WK 3 OCT FY2013 get converted to Oct 15 -21
WK 4 OCT FY2013 to Oct 22-28
WK 5 OCT FY2013 to Oct 29- Nov4
View 4 Replies
View Related
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
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
Mar 8, 2014
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
View 4 Replies
View Related
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)
View 3 Replies
View Related
Dec 13, 2011
I am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies
View Related
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
View 9 Replies
View Related
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?
View 9 Replies
View Related
Sep 29, 2006
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 Replies
View Related
Oct 21, 2009
1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)
2 Year/month into decimal year/month
I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.
View 9 Replies
View Related
