DATE Functions???

how will i classify the dates according to its quarter. lets say
10-02-03 (october 2, 2003) is it under 1st quarter, 2nd quarter, 3r
quarter,4th quarter of the year? what date function should i use? o
what do i have to do? pls help me with this. than

--
Message posted from http://www.ExcelForum.com

0
5/6/2004 1:07:45 AM
excel 39879 articles. 2 followers. Follow

2 Replies
629 Views

Similar Articles

[PageSpeed] 1

Hi malay_ko!

We can use an easily adaptable formula:



=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)



It should be clear that all you need to do is assign arguments 1,2,3 and 4
to all of the months in turn. At present the formula puts Jan, Feb. Mar into
Q1, Apr, May, Jun in Q2 etc.



So if you want Q1 to start in Oct use:



=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)




-- 
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"malay_ko >" <<malay_ko.15t86x@excelforum-nospam.com> wrote in message
news:malay_ko.15t86x@excelforum-nospam.com...
> how will i classify the dates according to its quarter. lets say,
> 10-02-03 (october 2, 2003) is it under 1st quarter, 2nd quarter, 3rd
> quarter,4th quarter of the year? what date function should i use? or
> what do i have to do? pls help me with this. thanx
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
5/6/2004 1:32:55 AM
Hi
try
=ROUNDUP((MONTH(A1)/3),0)

-- 
Regards
Frank Kabel
Frankfurt, Germany


> how will i classify the dates according to its quarter. lets say,
> 10-02-03 (october 2, 2003) is it under 1st quarter, 2nd quarter, 3rd
> quarter,4th quarter of the year? what date function should i use? or
> what do i have to do? pls help me with this. thanx
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
frank.kabel (11126)
5/6/2004 4:13:23 AM
Reply:

Similar Artilces:

Transaction Dates Splits
If i have a cash withdrawl on Nov 30th and want to split this transaction into different categories for where i spend the cash. The problem lies in that when I add part of the cash to a category it stays in the month where the withdrawl was taken. I want the new categories to relflect December spending. HELP ? This is a multi-part message in MIME format. ------=_NextPart_000_00C7_01C4D7F6.4132A020 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable You want to keep track of where you're spending your cash, right? What = you need to do...

Custom Date Range for Budget
Hello, I use 2003 Deluxe for my personal finances. I recently volunteered to be the PTO treasurer and was hoping to use Money to keep track of our checkbook register and budget, and also to use it for reporting. Is there any way to set custom dates for a budget period? I see that I can choose "this year" or "last year". But our fiscal year runs from July-June and I'd just like to look at a yearly budget in that time frame. Thanks! Maria OK, I am going to answer my own question. I played around some more and realized I can use a monthly report and set c...

Payables Future Dated Invoices
We have a client who needs to date an invoice in the future, but pay it now. For example the rent invoice is dated 8/1, but they must create a check for it now. Is there something we can setup in Payment Terms so that the invoice will come up in Select Checks to be paid? Version is 10, SP1. Thank you. When executing your check run, enter a due date cutoff of invoice date plus the number of days due on the payment term. For example, if the invoice date is 8/1 and the due date is 9/1 then you should enter a cutoff date of 9/1. The downside is that this may show additional documents th...

combining dates and text in a cell
Hello-- I had a column with dates and another with text labels. I'd like to combine these and tried using this formula: =D6&" "&I6 D6 = 7/4/2009 I6 = HOLIDAY Is there any way to do this to get the actual date and not the numberical value returned? Thanks in advance for your help. try =TEXT(D6,"m/d/yy ") & I6 Notice that I've included the space for separation as part of the format for the date, saving a little complexity of the formula. You can, of course, use other formats for the date to get it displayed differently, s...

Trending Function?
I'm trying to have a six month forcast of storage used on a server. I have range D3:O3 as storage values assigned for 12 months I have range D2:O2 as the date each of the storage values were pulled. I would like to have a formula in cells p3,q3,r3,s3,t3,u3 that will give me a forcast for 1 month out, 2 months etc. Would I use the Trend formula for that? Thanks, Kevin Green Kevin wrote: > I'm trying to have a six month forcast of storage used on a server. > > I have range D3:O3 as storage values assigned for 12 months > I have range D2:O2 as the date e...

Calling a function via Key-stroke (eg Ctrl + S)
I want the user to invoke a function, via keystroke, rather than menu / button. How do I do this? Hi Hamish, override PreTranslateMessage and then: if (pMsg->message == WM_KEYDOWN) { if (pMsg->wParam == VK_F9) //for F9 YourFct(); } Regards Matthias Hi Hamish, override PreTranslateMessage and then: if (pMsg->message == WM_KEYDOWN) { if (pMsg->wParam == VK_F9) //for F9 YourFct(); } Regards Matthias If you want it application-wide, add an acccelerator to the accelerator table in the resource file. "Hamish" <h.dean@xtra.co.nz> wrote in...

Date in Header/Footer
Does anyone know how to put the latest "revision date" in the header or footer. All I can get is the date printed or any text I may put in. I would like it to automatically update the header/footer with the date any changes are made. Please help, Nick Hi Nick, Private Sub Workbook_BeforePrint(Cacel As Boolean) With ActiveWorkbook .ActiveSheet.PageSetup.LeftHeader = "Last saved on: " & _ Format(.BuiltinDocumentProperties("Last Save Time"), "dd mmm yyyy") End With End Sub goes in the ThisWorkbook code module -- ...

How to correct an incorrect statment date in 401k manager (vs 2002
Greetings. I'm wondering if anyone can help me. I was a little behind updating my statements for my 401k. When I went to do the 12/31/2004 statement I accidentally entered the date of 12/31/2005. I didn't notice until I finished and tried to do 1/31/05. I get an error that says I can't enter a past date. I can change all the transactions entered to 12/31/04, but can't seem to change the last update data. Anyone know how to correct this so I can update the statements in 2005. Thanks, J ...

Advanced Outlook right click functionality from system tray
There are some actions i would like to have available through a right click interface on the outlook icon on the system tray: Notify me about new (unread) emails. Manual send \ receive Create new mail The reason for this is that sometimes restoring Outlook from the system tray takes too much time and resources from my system, when all i wanted to do is a simple action that does not require Outlook to load it's full interface and all my emails. I'm using Microsoft Outlook 2007. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the...

Displaying dates by weeks in Report
I have to display data by weeks in a report. What is the best way to do it? weeks 5/24/2010 - 5/28/2010 5 time sheets returned On Fri, 28 May 2010 06:10:01 -0700, Alaska1 <Alaska1@discussions.microsoft.com> wrote: I use a function that turns a date into a weeknumber in the format yyyy-ww, which I can then group by. The function uses the Format function to do this conversion. -Tom. Microsoft Access MVP >I have to display data by weeks in a report. What is the best way to do it? > >weeks >5/24/2010 - 5/28/2010 5 time sheets returned Is that a...

Purchase Order Promised Date
We have sales documents commited to purchase orders. On the sales document, the promised date on the purchase order link is not the same date as what is in the purchase order for the promised date. Is this a bug or am i doing something wrong? ...

Decoding function
Here is some decoding function, Dim b Function c(d) c=chr(d) End Function b=Array(c(204),c(224),c(128),c(056),c(093),c(131),c(232),c(098),c(033),...c(000 ),"") Can someone explain this function and purpose of each line before decimal coded ASCII. Also, what need be changed if change Ascii encoding method to HEX encoding? (like this 71 77 74 72 79 72 20 74 75 74 6e 6d 6e 6d 2c 72 75 72 75 69 72 75 72 75 20 34 32 34 32 ..) On May 17, 12:31=A0pm, "anime" <an...@nospam.microsoft.news> wrote: > Here is some decoding function, > > Dim b ...

Cell values as criteria for SUMIF and other functions
Hi, I have just changed jobs and am now working on XP instead of office 2000. I used to be able to write a formula such as: =SUMIF(A2:A10,<D2,B2:B10) I can't seem to be able to reference a cell for a criterion when the operator is anything other than "=". Can somebody point me in the right direction. Thanks & Regards, Will "Will" <william.cull@core-bi.com> skrev i en meddelelse news:1153742142.472000.75490@m73g2000cwd.googlegroups.com... > Hi, > > I have just changed jobs and am now working on XP instead of office > 2000. I used to be...

Date Formatting
With the help of Biff yesterday I entered the formula ="Closing Balance at "&TEXT(E4,"dd-mmm-yyyy") which returned Closing Balance at 31-Mar-2010 exactly as I wanted - almost. My superiors require that to conform with the rest of the document the month element of the date be in upper case, i.e. Closing Balance at 31-MAR-2010 I have tried, in vain and in desperation, the following - ="Closing Balance at "&TEXT(E4,"DD-MMM-YYYY") and ="Closing Balance at "&TEXT(UPPER(E4),"dd-mmm-yyyy") but these do not have th...

disallow cheque date to differ from invoice document date
This suggestion came from one of our clients. They are very upset that the system allow a cheque/cash payment (in AP) or cheque/cash receipt (in AR) to have a different date from the date on the invoice that the on-the-fly cheque/cash is attached to. The product suggestion is to force the cheque/cash payment/receipt date to be the same as the invoice date (which is the same as the GL posting date in this case), or at least create a setup option to either allow or disallow different cheque/cash date from the invoice. The scenarios was that the client has entered many AR invoices da...

how can we calculate the numbers of hours between two dates and
i need to calculate the exact number of hours between two different dates and times, e.x. 04/12/10 10:23 AM and 04/15/10 15:18. =B1-A1, format Custom as [h]:mm -- Kind regards, Niek Otten Microsoft MVP - Excel "Rama Chandra" <Rama Chandra@discussions.microsoft.com> wrote in message news:1AE69E13-2B22-4CBA-9E0B-3B2665C466E1@microsoft.com... >i need to calculate the exact number of hours between two different dates >and > times, e.x. 04/12/10 10:23 AM and 04/15/10 15:18. > =(+A2-A1)*24 -- Hope this helps. If it does, please click th...

curve fitting to a sine function
Is there a way to fit a curve to a fuction such as y= A+B*sin((2*PI*x/D) +C)^(-e*x) + F*x in Excel, or is this too difficult to do in Excel? In article <B814291D-401E-4B6C-B29F-F5B99F623290@microsoft.com>, Tenarife@discussions.microsoft.com says... > Is there a way to fit a curve to a fuction such as > y= A+B*sin((2*PI*x/D) +C)^(-e*x) + F*x > > in Excel, or is this too difficult to do in Excel? > See PLOT manager http://www.tushar-mehta.com/excel/software/plot_manager/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tuto...

How do I enter a sum function for statistics in Excel
Hi, I'm trying to enter a simple Sum function in Excel...by sum function mean the math notation of a capital case Sigma basically, I want to compute Sum (d)(L-x) for all values from x=0 t x=L. How can I enter this formula in excel? Thanks for any help o advice anyone might have! -Ti -- Tim12 ----------------------------------------------------------------------- Tim128's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1490 View this thread: http://www.excelforum.com/showthread.php?threadid=26534 Are both d & L an Array of numbers, or are they just co...

Date Changing
I am tearing my hair out over this problem so any ideas would be greatly appreciated. All of our Swiss users have the same problem when opening a spreadsheet. A date field on a protected worksheet in a protected workbook changes from 10-October-2003 to 00- January-1900. All of the fields are formatted correctly and when I open the same spreadsheet it displays the correct date. They suggested that the same problem had been logged before and the fix maybe in the excel or regional setup but I have been unable to find anything relevant in our call archives. Thanks in advance Mike ...

Attaching Help File to a User Defined Function
Hi, I wrote some functions in VBA for Excel and I would like to attach help file to each function. One solution was to attach the help file in the VBA project properties but I am allowed to attach just one file per project. As I have 4 functions in that project (they all have to reamain in the same VB project)... I have to find another solution. What would the solution be? Tks. Maca. :confused -- mac ----------------------------------------------------------------------- maca's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2489 View this thread: http://w...

Using COUNTIF function on large text fields
Hello all, I am trying to use a countif function to count the number of occurrences of a specific phrase within a column of data: =COUNTIF(<column>,"*text*") The problem I am encountering is that the countif function stops searching after the first 254 characters in each field. Any ideas how to get around this? Thank you in advance. ...

How to get records from Local Access Databse Using OPENROWSET Function
Dear Sir, I am using OPENROWSET Function to open Access Database File and my Query is as follows. SELECT Field1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\Sanjay\My Projects\Settings.mdb'; 'admin'; '', AppVersion) If I run this application on client computer and the specified file is exist on client computer then it display error as follows because it tries to search file on server [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message &q...

unknown xslt function "format-dateTime"
Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' is an unknown XSLT function" at the following line: <xsl:value-of select="format-dateTime($pubDate,'[FNn] [D] [MNn] [Y] [h]:[m01] [PN]')" /> how can I get around this? > > Iam using Beta2 of VS2005 and getting the error "Error 4 'format-dateTime()' > > is an unknown XSLT function" at the following line: > > format-dateTime() is XPath2.0/XSLT2.0/XQuery1.0 function. VS2005 only > supports XPath1.0/XSLT 1.0. Use ms:format-date() exten...

If Worksheet function
I am using the IF worksheet function to do a vlookup to obtain certai values for an entered cell. I am limited to 7 by default. is ther another may to obtain this data. I have 9 different input options t obtain several output -- Lin ----------------------------------------------------------------------- Lins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2422 View this thread: http://www.excelforum.com/showthread.php?threadid=48941 On Wed, 30 Nov 2005 08:36:27 -0600, Lins <Lins.1zbdsm_1133361600.7943@excelforum-nospam.com> wrote: > >I am using th...

Filter on date problem
Hi I am trying to display records whose ring-back-date is today. Here is my code: Dim strDate As String strDate = Format(Date, "dd/mm/yyyy") Me.Filter = "[Ring-back-date] = " & strDate Me.FilterOn = True The field [Ring-back-date] is defined as Short Date, input mask 00/00/0000;;_ Stapes On Thu, 3 Jan 2008 05:44:47 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: You're not stating what problem (if any) you are receiving, any error messages? I'm assuming the filter does not work because you don't have #-signs around the date: M...