Converting DD/MM/YYYY to FY and Quarter

[#1]

Hi,

I using Personal edition and has a excel sheet to be loaded. over here, I will illustrate a small subset of the data. There are 2 sets of questions

1) I will like to convert the screeningDate to FY and Quarter according to the financial year like Apr - June will be Q1, July-Sep will be Q2, Oct - Dec will be Q3 and next year Jan - Mar will be Q4. let say for Customer A of 15/04/2014 it will be FY14Q1 and so on.

2) I also like to know for the duplicate/same customer what is the quarter difference apart. for instance, for Customer A it will be FY14Q4 - FY14Q1 =3Q. My dataset extend till 2016 as of now.

For your kind assistance.

Customer	ScreeningDate
A	15/04/2014
B	15/06/2014
C	18/07/2014
D	19/09/2014
A	30/02/2015
B	05/05/2015
C	08/05/2015
D	19/10/2015

[swuehl]

First, read your ScreeningDate values in correctly interpreted dates:

Get the Dates Right

Then create a fiscal year master calendar, there are some samples here in the forum, like

Fiscal Year

(see also the samples in the comment section)

Quarters can also be created using QuarterStart() function

quarterstart - script and chart function

This function returns a value corresponding to a timestamp of the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.

Syntax:

QuarterStart(date[, period_no[, first_month_of_yea]])

where you can set the first month in FY.

If you Autonumber() your quarters, you can also just subtract these field numbers to get a difference in quarters for e.g. Customer A records.

Hope this helps,

Stefan