Formating Fiscal Quarters, rather than Months

I would like to be able to format a date based upon fiscal quarter, rather 
than simply Month year.   This would be based upon the underlining data:  For 
example, what I would like to see:

Q1FY06  Q2FY06  Q3FY06   Q4FY06

Would be created by inputing:

11/1/2005   02/01/2005    05/01/2005    08/01/2005

Once formated they would show as above.  Otherwise, I will define a named 
field, and go that way, but I have a lot of these headings to do, and 
formatting them would be simplest way to get them to work, if possible. 

Is there any sort of custom formatting that would do this?

Thanks!

PatK
0
PatK1 (17)
9/7/2005 12:07:21 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
419 Views

Similar Articles

[PageSpeed] 27

I don't think you'll be able to do this by formatting alone--but you could use a
formula in a helper cell to display your FYQtr.

Personally, I think
FY2006-Q1
is nicer--I like 4 digit years and by putting the year first, I can sort nicely.

If you agree, you could use this formula:
="FY"&YEAR(A1)+(MONTH(A1)>=11)&"-Q"&INT(1+MOD(MONTH(A1)-11,12)/3)

If you disagree, you could use this formula:
="Q"&INT(1+MOD(MONTH(A1)-11,12)/3)&"FY"&RIGHT(YEAR(A1)+(MONTH(A1)>=11),2)


PatK wrote:
> 
> I would like to be able to format a date based upon fiscal quarter, rather
> than simply Month year.   This would be based upon the underlining data:  For
> example, what I would like to see:
> 
> Q1FY06  Q2FY06  Q3FY06   Q4FY06
> 
> Would be created by inputing:
> 
> 11/1/2005   02/01/2005    05/01/2005    08/01/2005
> 
> Once formated they would show as above.  Otherwise, I will define a named
> field, and go that way, but I have a lot of these headings to do, and
> formatting them would be simplest way to get them to work, if possible.
> 
> Is there any sort of custom formatting that would do this?
> 
> Thanks!
> 
> PatK

-- 

Dave Peterson
0
petersod (12004)
9/7/2005 1:08:34 AM
Thanks, Dave!!  I will give it a try!

"Dave Peterson" wrote:

> I don't think you'll be able to do this by formatting alone--but you could use a
> formula in a helper cell to display your FYQtr.
> 
> Personally, I think
> FY2006-Q1
> is nicer--I like 4 digit years and by putting the year first, I can sort nicely.
> 
> If you agree, you could use this formula:
> ="FY"&YEAR(A1)+(MONTH(A1)>=11)&"-Q"&INT(1+MOD(MONTH(A1)-11,12)/3)
> 
> If you disagree, you could use this formula:
> ="Q"&INT(1+MOD(MONTH(A1)-11,12)/3)&"FY"&RIGHT(YEAR(A1)+(MONTH(A1)>=11),2)
> 
> 
> PatK wrote:
> > 
> > I would like to be able to format a date based upon fiscal quarter, rather
> > than simply Month year.   This would be based upon the underlining data:  For
> > example, what I would like to see:
> > 
> > Q1FY06  Q2FY06  Q3FY06   Q4FY06
> > 
> > Would be created by inputing:
> > 
> > 11/1/2005   02/01/2005    05/01/2005    08/01/2005
> > 
> > Once formated they would show as above.  Otherwise, I will define a named
> > field, and go that way, but I have a lot of these headings to do, and
> > formatting them would be simplest way to get them to work, if possible.
> > 
> > Is there any sort of custom formatting that would do this?
> > 
> > Thanks!
> > 
> > PatK
> 
> -- 
> 
> Dave Peterson
> 
0
PatK1 (17)
9/7/2005 4:08:02 AM
Reply:

Similar Artilces:

Cond Format: Expiry Date Alert
Does anyone know how to solve this one? I have passport expiry dates and I would like to conditionally format them so that 6 months before the passport expires, the cell containing the expiry date changes red. Thanks -- ChrisTMI ------------------------------------------------------------------------ ChrisTMI's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10342 View this thread: http://www.excelforum.com/showthread.php?threadid=481899 use conditional formatting "ChrisTMI" wrote: > > Does anyone know how to solve this one? > > I h...

Display Month in a label driven from data series
Can an independent label have a formula reference that looks at a particular cell and displays that value?. I am importing data on a monthly basis, that over rights existing data (the existing data is copied and stored for refernece) and have a chart which updates for that months data. I can automate the printing of the chart but what I wish is that some label on the chart shows the month that the data is for. The label looks at a cell and shows that value. If it can, what is the format for the expression ? in the label, better still where do I start, I'm thinking along the lines ...

Trouble formatting Mail merge Number field for european format
I have to mail merge a letter using XLS as datasource that has some currency fields and the output of the currency fields should be formatted to european format (eg: 1.234.567,99) Please note that decimal separator is comma and group seperator a dot. If could not find a suitable switch. Can someone please point me to right place? O/S: Windows XP MS Office 2003 The European format would be; 1,234,567.99 and not the decimal seperator being a comma "SRV" <SRV@discussions.microsoft.com> wrote in message news:91B4D91E-294F-4F6B-A131-743D3171A7E2@microsoft.co...

how do i type a time into a cell formatted for time?
I have highlighted a block of cells and used format-cell-time to format for time but when I try to type in a time using the 24 hr clock eg. 0800 it converts the time to a date. What am I ding wrong? You're doing nothing wrong - except for Text (which bypasses XL's entry parser), the display format has no effect on how an entry is parsed. For a way to accomplish what you're after, see http://cpearson.com/excel/DateTimeEntry.htm In article <B52639A5-5BA2-46F7-8407-DBA6554B21EE@microsoft.com>, Armadillo <Armadillo@discussions.microsoft.com> wrote: > ...

[b]calculating Month Average For Exch Rates From Data Array
[B]Hello, All, need Yr help in formula creating. We have exchange rate calendar (USD to Ukranian Hryvna) by every banking day in the year. Data organized as following: col A - dates DD/MM/YYYY, col B - appropriate exchange rate, for instanse - 5,05. I need to have in col C average month exchange rate. So do i need to recognize that date belongs to the same month in the same year and then to include appropriate rate to array for calculating an average? that's my idea... Please comment thanx in advance -- StanUkr ------------------------------------------------------------------------...

voiding checks and fiscal periods closed
Hello: I know that if the fiscal period of March is closed in the Fiscal Periods Setup window that payables checks cannot be voided for March. Now, if a payables person cannot void checks in April and the fiscal period of April is not closed, could that be because the invoice that the check is being applied to is dated in March. That would make sense based on how payables would throw the check back into open. But, I just wanted to check with you all. Thanks! childofthe1980s You are correct. -- Charles Allen, MVP "childothe1980s" wrote: > Hello: > > I know ...

How to format cells in upercase entry
Hi I read the post for this question (8/8/2006) and Gord Dibben's answer works great. Original Question: I need to format a column so that all cell force any text entry to uppercase. Gord Dibben's Answer: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column > 8 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub This code allows all columns up to column 8 (column H) to be affected What I would like to know is can I nominate a p...

Word 2007 Autotext and formatted text
I type the underlined text, select the text, hit Alt-F3, it's saved in Building Blocks, I name it and save it. When I go back to enter the text, either in the same document or in a new document days later, the text is NOT underlined. I have repeated the entry over and over. It never holds the underline. Does 2007 not hold formatting in autotext? Thanks for your help. How did you apply the underline formatting? Direct formatting (for example, applied by pressing Ctrl+U) should definitely be saved to the AutoText/Building Block entry. -- Stefan Blom Microsoft Word MVP ...

Numeric sign placement in custom format
I would like to format some cells with the text prefix "X = ". I've done this many times in the past with no problems, but apparently never had any negative numbers. The custom format string I am using is ["x = "0.000]. For positive numbers, it works fine. But for negative numbers, the "-" sign is placed before the "x": -x = 3.45 instead of before the number x = -3.45 Can someone help me fix this? "Jennifer Murphy" <JenMurphy@jm.invalid> wrote: > The custom format string I am using is ["x = "0.000]. > For ...

Need help. Conditional formatting problems
I have setup a worksheet that contains QC information. I created a simple "form" for the techs to enter there data into and then that "form" is repeated at least 52 (52 weeks in a year) times on the same worksheet. Setting up conditional formatting is very time consuming and I need a faster easier way to do it. This is what my "form" looks like: ------------------------------------------------------------------------------------------------ Week 1 Date set _*12/28/09_ Organism tested (E.coli) Initials __DA___ Drug tested ...

monthly fee for money
I want to change the way I pay the monthly fee for money since I am closing that bank account. How do I do that? -- Linda VDS In microsoft.public.money, Linda VDS wrote: >I want to change the way I pay the monthly fee for money since I am closing >that bank account. >How do I do that? Money does not charge a monthly fee. I assume you are paying that to your bank. If you move to another bank, I guess the fee would go away. Otherwise you should discuss that with the bank. In microsoft.public.money, Linda VDS wrote: >I want to change the way I pay the monthly fee for money sin...

How do I format column labels?
A spreadsheet is showing the column headers in numbers. How can I change them back to letters? Tools | Options |General and clear R1C1 Reference Style "Shella" wrote: > A spreadsheet is showing the column headers in numbers. How can I change them > back to letters? ...

Date Formula to Count Months
Hi, Can someone help me with a simple date formula? I need to calculate the number of months an employee has been on the job. If they started on the 15th of the month or after, we don't count that month as a month worked. If they leave on the 15th or later, we DO count that as a month worked. My result needs to be a whole number and I want to enter the start date in one cell, the term date in another and have number of months worked appear in another cell. Hi try using DATEDIF. See: http://www.cpearson.com/excel/datedif.htm -- Regards Frank Kabel Frankfurt, Germany Mark wrote: ...

Advice, help on code using winapi and month-calendar-object of Comctl32.dll
Hello, I tested the following VBA-code (see below) in excel 2000 on Windows XP. And it works The code is used in a userform called CalendarUserForm (height 216 points, width 170 points) with two CommandButtons (OKButton, CancelButton) on the userform.On the userform, the code creates a month-calendar-control using the WIN-API. I know i could use the Microsoft MonthView Control ActiveX-object in MSCOMCT2.OCX. But it is difficult to distribute and install these ocx-files on other systems. info about the month-calendar-object: http://msdn.microsoft.com/library/default.asp?url=/library/en...

Date Format #17
I would like to convert a date that I've input from another program. When I input it I get 'Aug-28-2008. I want it to just be a regular date - so it would be *8/28/2008 if I were to select it in Format Cells. It says that I have General as the Category selected. When I switch it to Custom - nothing seems to happen. Any suggestions? First create a small table in the worksheet: Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 11 Dec 12 and assign the name: nmths to the table. Then, with your data in A1, try: =DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),mnths,2,FALSE...

Formula--rather simple one
The formula is adding and subtracting numbers across the rows. The problem is that when "some" of the cells containing numbers that are included in the formula are deleted, changed--the result of the formula does not change. For instance, the number in cell BW is deleted and nothing happens. Is there something wrong with the way this formula is set up? Could there be corrupt data? =+B10+SUM(C11:CJ11)-SUM(CL11:FE11) -- Wanda Garner Highmark Blue Shield Press "F9"... does the value change now? If so, you have automatic calculation turned off... <Tools><...

Cell Format 05-25-10
I want to enter <shift>: in a cell (this would be Shift Key and the colon character to enter a date). And then automatically format this cell so that the date is on one line, and the value "Critical Date" on the second line. So the cell value would look like this: 05/25/2010 Critical Date Can someone explain how to do this? If it can be done with code, I would be interested in that also... First, ctrl-: (control-colon) will enter the time, not the date. You want ctrl-; (control-semicolon) to enter the date. Second, you can use a custom number format to...

Apply Conditional Format to all sheets in same workbook
Is there a way to apply a conditional rule to all of the worksheets in a workbook? In other words, the cell range and format is the same for each sheet but I'd prefer not to create the rule for 40 pages. Thank you in advance. A CF is a format like any other format. Create one page and then copy and paste to the rest of the pages... -- HTH... Jim Thomlinson "Der Musensohn" wrote: > Is there a way to apply a conditional rule to all of the worksheets in a > workbook? In other words, the cell range and format is the same for each > sheet but I&...

Setting an Icon Set conditional format for a single cell???
This is my first question here and I'm pretty new to Excel, but I have a question about the Icon Set conditional formatting. From what I understand it's intended for use of a range of cells. However, I have a single cell that I want to display 1 of the 3 light icons for based on a certain condition. For example, this cell is a percentage. If the percentage is greater than 80%, display a green light. If the percentage is greater than or equal to 60% but less than or equal to 80%, display a yellow light. If the percentage is lower 60%, display a red light. Steps that I've done so ...

Date Format Doesn't Work
Vista Business, Office 2007. On one Vista of my three machines (2 Vista, 1 XP Pro SP2) date formatting does not work. I have tried a blank sheet. I have tried the other 2 machines they work both with blank sheets and with the a sheet saved on the problem machine. I have run repair without any improvement. Still the date will not format. With the Ctrl+; shortcut the date appears as set in regional settings. This has occurred (and started) within the last two weeks. (Sorry I don't recall exactly when the problem started. Any suggestions? TIA I'd try formatting the cell as Gene...

Having trouble formatting times
I've received a few good pointers, but haven't found the magic combination yet. I want to be able to enter times without using any seperators. I have the VBA code that does that, but the formats aren't coming out right. Here's what I'm getting: (number on left is raw input for cell, value on right is result) 1 - 00:01:00 12 - 00:12:00 123 - 01:23:00 1234 - 12:34:00 12345 - 01:23:45 123456 - 12:34:56 Here's what I want to get: 1 - 00:00:01 12 - 00:00:12 123 - 00:01:23 1234 - 00:12:34 12345 - 01:23:45 123456 - 12:34:56 How can I get that result? -- John Oliver, C...

Conditional chart formating
I need to format my charts so the color of the data points change depending on the value amount. My tabels are already set up and cannot be change or have anything new added due to the amount and delicacy of data. Is there a macro I can use. Perhaps a formula I can put somewhere? Hi, Ed Ferrero has a vba example. It's for a column chart but the code will be very similar. Instead of interior fill you will be doing marker stuff. eg: .MarkerBackgroundColorIndex = 50 .MarkerForegroundColorIndex = 50 Conditional Chart Formatting example. http://www.edferrero.com/charti...

Conditional Formatting
Using conditional formatting in a group of cells, I know how to make the highest number appear in bold type: =MAX($C$14:$C$23), then format BOLD. Is there a way to apply this condition to a chart? thanks Atom, It can be done via an event macro. If you're willing, we can give you some code. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "atom" <slkdjfs@asdfa.net> wrote in message news:3FB130AE.AF630C10@asdfa.net... > Using conditional formatting in a group of cells, I know how to make the > highest numb...

Conditional formats won't print
I am using Excel 2003 (11.6355.6360) SP1 I have a spreadsheet with cells conditionally formatted to turn green or red. Everything looks fine on the screen...but when I print, I don't see any of the colors..... The cells show up without any color formatting (ie they look just like any other non-conditionally formatted cell). Not sure what has changed b/c I've printed conditionally formatted spreadsheets in color in the past. Anyone have any ideas? "consumer" wrote: > I am using Excel 2003 (11.6355.6360) SP1 > > I have a spreadsheet with cells conditional...

troubleshooting date formats
I am having major problems with formatting dates. When I enter 10804 and try to format the cell (format,cells,number.date,03/14/98), the number in the cell changes to 07/30/29. When I type 1/08/04, it changes to 3-79-94. What is going on?? I tried going into tools, options, calculations, 1904 date system to see if the date program was off but when I tried that, it changed 01/08/04 into 1/9/04. I am at a complete loss and am going crazy. I am entering dates as text right now, but I need to use these dates in calculations. Hi basically, excel stores dates as a number, 1 being the 1...