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 RepliesI 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
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.
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 RelatedI need to define a specific calendar quarter and year, and want to do so using a combo box for the quarter (e.g. 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter) and a list box for the year (so the user can select any year desired).
The selections in each item should then somehow define the quarter's date range so I can use it in my SUMPRODUCT calculations.
Example:
User selects '1st Quarter' from the Combo Box
User selects '2004' from the List Box
CurrentQuarter range is somehow defined to equal 1/1/04 through 3/31/04.
If this isn't possible, then perhaps two ranges can be defined based on the user's selections:
QtrStart is set to 1/1/04
QtrEnd is set to 3/31/04
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.
The formula I started with is -
=IF(A1=0,0,AVERAGE(A1:A3)
This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
AVERAGE?
Is there a formula to set the a date in Excel based off the next full calendar quarter? In cell G2 I have the contract date: 01/26/06. I would like to have a formula in I2 that displays the first day of the next full calendar quarter, which is 04/01/06
More example dates:
if my contract date is: 10/21/05
my start date is: 01/01/06
I have just finished sorting my anniversary list by Month (in alpha order)
B C D E F
1 Hire Date MonthDay YearLast Name First Name
2 04/04/2011April 042011Emp 1
3 04/09/1996April 091996Emp 2
4 08/02/2012August022012Emp 3
5 08/09/2004August092004Emp 4
6 08/13/2001August132001Emp 5
7 08/16/2010August162010Emp 6
Is there a way to sort by Calendar year? Jan, Feb, Mar, etc.
I have found the answer to several of my excel questions here in the past, but I'm completely stumped on this one.
I have two dates:
Jan-08
Sep-08
And I need to know if the range of these dates (January-September) falls between the range of two other dates:
Jan-08
Jun-08
Basically I'm checking to see if the range of months Entered falls in the First half of a year or the second, or both
I have come accross this calendar online and it suits my requirements, however, the month s currently static and the year is dynamically controlled by a spin button.
The formulas in the relevent days are as follows
Day 1 =IF(AND(YEAR(JanOffset+1)=calendarYear,MONTH(JanOffset+1)=1),JanOffset+1,"")
Day 2 =IF(AND(YEAR(JanOffset+2)=calendarYear,MONTH(JanOffset+2)=1),JanOffset+2,"")
and so on, I need the formula and the spin button the also change the month as well as the year.
Please see attached file for details.
how I would display a simple msgbox to a user if a date they select from monthview isn't a date I want them to select. They can only chose dates between aug and dec 07 and i have the code lines
If MonthView1.Month = "12" And MonthView1.day = "15" Then december15.december15
I have a user form that has a calendar button. Once clicked it updates a txtbox on my user form. What I would also like is for another txtbox (txtQtrLeave) to be auto populated based on this entry. Below is the code I've tried:
If CalendarEnd.Calendar1.Value > #3/31/2008# And CalendarEnd.Calendar1.Value < #1/6/2008# Then
Qtr = 1
frmAddClient.txtQtrLeave.Value = Qtr
... Qtr 2, 3 etc
It seems to return a value if I have one criteria but it doesn't work once I add the And element. What am I doing wrong (this has been driving me mad all morning)?
I'm trying to figure out how to identify higher earnings in a quarter and return the max of the quarter of time frame and not the value
I'm simplifying a little ...
How would you calculate the amount of days of a billing period for a consulting engagement by quarter when the engagement could span multiple quarters, be within a quarter or not be in a quarter at all? Project based billing
View 1 Replies View RelatedI have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
Using Excel 2007 Calendar Control:
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer
Insert
More Controls
Calendar Control 12.0
I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
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 RelatedI 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.
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?
I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.
I have the original file Calendar source code if anyone needs it.
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.
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
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies View RelatedI am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies View RelatedI 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.
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
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):
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 RelatedI 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)