Help with date range for fiscal year

[Michael Fulton]

Hello,

I am trying to copy only the year from a column with dates in the format of ddmmyyyy into a new column. The existing date column in the format ddmmyyyy is called GSGiftDate.

Lets call this new column "fiscal_year".

However, I cannot simply use the gen fiscal_year = year(GSGiftDate) command. This command works for copying the same year as the column with the full ddmmyyyy date, but the fiscal year depends on the day and month range as well.

Example: A fiscal year in the way I'm using it is defined as July 1st to June 30th (7/1 - 6/30).

7/1/2013 to 6/30/2014 is Fiscal Year 2014
7/1/2014 to 6/30/2015 is Fiscal Year 2015
etc.

How do I give Stata a date range in which it knows to assign the second year in the range 7/1/yyyy - 6/30/yyyy+1 to this new fiscal_year variable? (For instance in range 7/1/2013-6/30/2014, fiscal_year would receive the value of 2014.)

Thank you

* Example generated by -dataex-. To install: ssc install dataex
clear
input long PPidm int GSGiftDate double GIFTSPAYMENTS
70004539 15487 1925
70004539 15496   40
70004539 15630  125
70004546 17118   88
70004546 18210   45
70004546 18574   45
70004546 18911   45
70004546 19333   45
70004546 19737   45
70004546 20645   75
70004546 21013   75
70004546 21063  100
70004547 20863   20
70004591 20478   50
70004605 19415  100
end
format %td GSGiftDate

[Michael Fulton]

The GSGiftDate did not copy properly but the dates are floats in the format 27may2002.

[Clyde Schechter]

gen fyear = yofd(GSGiftDate)
replace fyear = fyear+1 if month(GSGiftDate) >= 7

[Michael Fulton]

Thanks again Clyde!

[Nick Cox]

See also https://www.stata-journal.com/sjpdf....iclenum=st0394

[Romalpa Akzo]

Following Nick's article and also somewhere in this Forum.

gen fiscal_year = yofd(dofm(mofd(CSGiftDate)-6))

[Shiwani Varal]

Hi, I am using Stata 16. I am also working on converting the calendar dates to fiscal dates. In my country of observation, the fiscal year ranges as" FY 2008: 1st April 2007 - 31st March 2008. I found post #3 command and #5 document to be quite useful to compute fiscal year for my data:

gen fyear=yofd(edatevar)
replace fyear= fyear+1 if month(edatevar)>=4

However, I also need weeks for my data. At the moment, my data looks like this:

edatevar    week_id    returns    fyear   co_code
25mar2009    2009w12    4.97    2009    11
26mar2009    2009w13    5.01    2009    11
27mar2009    2009w13    5.01    2009    11
30mar2009    2009w13    5.01    2009    11
31mar2009    2009w13    5.01    2009    11
01apr2009    2009w13    5.01    2010    11
02apr2009    2009w14    3.07    2010    11
06apr2009    2009w14    3.07    2010    11
08apr2009    2009w14    3.07    2010    11
09apr2009    2009w15    3.14    2010    11
13apr2009    2009w15    3.14    2010    11
15apr2009    2009w15    3.14    2010    11

Here, edatevar: actual calendar date; week_id: week number for the calendar year and fyear: fiscal year

Is there a way I could generate a variable that allows me to start to assign the 1st April 2009 as 2010w1 in the Fiscal year? Currently week_id variable shows that this 01apr2009 observation is w13 observation, but it will be week 1 observation in fiscal year. I feel that I would need to somehow set 1st April 2009 as a start date locally, but I am not sure about it and don't even know how to do that. I would appreciate any suggestions you have for me? I hope my post is making sense, if not I am more than happy to explain more. Thanks!

[Nick Cox]

I take it that your rules are that week 1 always starts on 1 April, in which case week 2 always starts on 8 April, ..., and there will be a week 53 with either 1 or 2 days at the end of March.

* Example generated by -dataex-. For more info, type help dataex
clear
input float edatevar int fyear
17981 2009
17982 2009
17983 2009
17986 2009
17987 2009
17988 2010
17989 2010
17993 2010
17995 2010
17996 2010
18000 2010
18002 2010
end

format %td edatevar

gen wanted = ceil((edatevar + 1 - mdy(4,1,fyear - 1)) / 7)

list

     +----------------------------------------------------------+
     |  edatevar   week_id   returns   fyear   co_code   wanted |
     |----------------------------------------------------------|
  1. | 25mar2009   2009w12      4.97    2009        11       52 |
  2. | 26mar2009   2009w13      5.01    2009        11       52 |
  3. | 27mar2009   2009w13      5.01    2009        11       52 |
  4. | 30mar2009   2009w13      5.01    2009        11       52 |
  5. | 31mar2009   2009w13      5.01    2009        11       53 |
     |----------------------------------------------------------|
  6. | 01apr2009   2009w13      5.01    2010        11        1 |
  7. | 02apr2009   2009w14      3.07    2010        11        1 |
  8. | 06apr2009   2009w14      3.07    2010        11        1 |
  9. | 08apr2009   2009w14      3.07    2010        11        2 |
 10. | 09apr2009   2009w15      3.14    2010        11        2 |
     |----------------------------------------------------------|
 11. | 13apr2009   2009w15      3.14    2010        11        2 |
 12. | 15apr2009   2009w15      3.14    2010        11        3 |
     +----------------------------------------------------------+

[Shiwani Varal]

Hi Nick Cox, thanks so much! This is exactly what I was looking for!