Calculation based on Date field

[AndrewBruno]

I want to define a calculated field that returns a text value based on date ranges.

i.e. if InvoiceDate is after 31/03/2001 and before 01/07/2001, "A" is returned and so on. (I'm calculating which quarter of our financial year the invoice relates to).

Cannot get this to work using either the If function or the Case function

A calculated field definition of

If(InvoiceDate>31/03/2001 and InvoiceDate<01/07/2001,"A","")

returns A for every record.

Puzzled by this one.

Andrew Bruno

[andygaunt]

Hi, how about these. one is based just on month, the second takes into account the year as well.

If( Month(Invoice date)>3 and Month(Invoice date)<7,"A","")

------

If( Month(Invoice date) >3 and Year(Invoice date) = Year(Status(CurrentDate)) and Month(Invoice date) <7 and Year(Invoice date) = Year(Status(CurrentDate)) ,"A","")

HTH

[AndrewBruno]

Yes that would work, but I want to identify quarter years for some time ahead and set this in the system.

I've actually solved it this way. As part of the opening script, I set a series of global date fields, g_quarter1, g_quarter2, etc. ( Insert text, anyway, as you can't set this field type !), marking the first day of each quarter.

These global date fields don't appear on any layouts.

My calculated field definition is then

Case(InvoiceDate>=q_quarter1 and InvoiceDate<g_quarter2,"A",InvoiceDate>=q_quarter2 and InvoiceDate<g_quarter3,"B", ........

I've set this up for 4 years ahead. I know I need to ensure that all machines in the network set the same globals.

This calculated field is needed to set up relationships to allow spreadsheet style analyses of sales by product by quarter and sales by customer by quarter. Is there an easier way I'm missing ?

Thanks for the usual prompt reply.

Andrew Bruno