[Solved] Find First Sunday or Last Saturday of a month.

March 26, 2012, 02:41 PM

peechy23

I am trying to calcuate the beginning and ending dates of my fiscal year for the next 60 years. This has presented certain challenges for me because our Fiscal Year year begins with the first week in the accounting month of February and ends after the last week in the next January's accounting month.

I have seen the formulas for getting the begining and ending of the month but I was wondering if there was an easy way to get the last saturday of January and then the first Sunday in February.

WebFOCUS 7.6
Windows
Output: Excel, HTML and PDF

This message has been edited. Last edited by: peechy23,

March 26, 2012, 03:13 PM

j.gross

My understanding is that your fiscal years end on the last Saturday of calendar January. The following code calculates January 31, determines its day of week, and adjusts the date accordingly to the last Saturday.

DEFINE FILE CAR
 SEQ/I4 WITH MODEL = SEQ+1;
 YEAR/YY = 2000 + SEQ;
 JAN01/WMDYY=YEAR;
 JAN31/WMDYY=JAN01+30;
 WD31/WTR=JAN31;
 NWD31/I1=WD31;
 NADJ/I1=IMOD(NWD31+1,7,'I1');
FYEND/WMDYY=JAN31-NADJ;
END
-RUN
TABLEF FILE CAR
PRINT
 YEAR
 JAN31
 NWD31
 NADJ
 FYEND
END

Output:

YEAR JAN31           NWD31 NADJ FYEND          
2001 WED, 01/31/2001 3     4    SAT, 01/27/2001
2002 THU, 01/31/2002 4     5    SAT, 01/26/2002
2003 FRI, 01/31/2003 5     6    SAT, 01/25/2003
2004 SAT, 01/31/2004 6     0    SAT, 01/31/2004
2005 MON, 01/31/2005 1     2    SAT, 01/29/2005
2006 TUE, 01/31/2006 2     3    SAT, 01/28/2006
2007 WED, 01/31/2007 3     4    SAT, 01/27/2007
2008 THU, 01/31/2008 4     5    SAT, 01/26/2008
2009 SAT, 01/31/2009 6     0    SAT, 01/31/2009
2010 SUN, 01/31/2010 7     1    SAT, 01/30/2010
2011 MON, 01/31/2011 1     2    SAT, 01/29/2011
2012 TUE, 01/31/2012 2     3    SAT, 01/28/2012
2013 THU, 01/31/2013 4     5    SAT, 01/26/2013
2014 FRI, 01/31/2014 5     6    SAT, 01/25/2014
2015 SAT, 01/31/2015 6     0    SAT, 01/31/2015
2016 SUN, 01/31/2016 7     1    SAT, 01/30/2016
2017 TUE, 01/31/2017 2     3    SAT, 01/28/2017
2018 WED, 01/31/2018 3     4    SAT, 01/27/2018

March 26, 2012, 03:26 PM

Dan Satchell

A Dialogue Manager approach:

-SET &YEAR = '2012';
-SET &FIRST_FEB     = &YEAR | '0201';
-SET &DOW           = DOWK(&FIRST_FEB,'A3');
-SET &ADD_DAYS      = DECODE &DOW ('SUN' 0
-                                  'MON' 6
-                                  'TUE' 5
-                                  'WED' 4
-                                  'THU' 3
-                                  'FRI' 2
-                                  'SAT' 1 );
-SET &FIRST_SUN_FEB = AYMD(&FIRST_FEB,&ADD_DAYS,'I8YYMD');
-SET &SUBTRACT_DAYS = DECODE &DOW ('SUN' 1
-                                  'MON' 2
-                                  'TUE' 3
-                                  'WED' 4
-                                  'THU' 5
-                                  'FRI' 6
-                                  'SAT' 7 );
-SET &LAST_SAT_JAN  = AYMD(&FIRST_FEB,-&SUBTRACT_DAYS,'I8YYMD');
-*
-TYPE &FIRST_SUN_FEB
-TYPE &LAST_SAT_JAN

March 26, 2012, 03:55 PM

peechy23

Thank you very much for the quick response on both of these. They are correct for most but I run into problem every now and then when we have a 53 week year instead of 52. I am not sure what the pattern is for that. Below is a list of what I crurrently have available out to 2018 for starting and ending dates.

Begining End FY Days in Year
2/4/2001 2/2/2002 2001 363
2/3/2002 2/1/2003 2002 363
2/2/2003 1/31/2004 2003 363
2/1/2004 1/29/2005 2004 363
1/30/2005 1/28/2006 2005 363
1/29/2006 2/3/2007 2006 370
2/4/2007 2/2/2008 2007 363
2/3/2008 1/31/2009 2008 363
2/1/2009 1/30/2010 2009 363
1/31/2010 1/29/2011 2010 363
1/30/2011 1/28/2012 2011 363
1/29/2012 2/2/2013 2012 370
2/3/2013 2/1/2014 2013 363
2/2/2014 1/31/2015 2014 363
2/1/2015 1/30/2016 2015 363
1/31/2016 1/28/2017 2016 363
1/29/2017 2/3/2018 2017 370
2/4/2018 2/2/2019 2018 363

Any thoughts on how I can incorporate this into a formula?

WebFocus 7.6
Windows
Outputs: Excel, HTML and PDF

March 26, 2012, 07:25 PM

Crymsyn

Looking at your latest post it seems to follow fairly closely to be the 5th week in a year.

I mainly edited j.gross's example to change from getting the FYEND to getting the start based off of the 5th week instead of the end of January.

DEFINE FILE CAR
 SEQ/I4 WITH MODEL = SEQ+1;
 YEAR_STA/YY = 2000 + SEQ;
 JAN01_STA/WMDYY=YEAR_STA;
 JAN31_STA/WMDYY=JAN01_STA+34;
 WD31_STA/WTR=JAN31_STA;
-* NWD31_STA/I1=WD31_STA;
 NADJ_STA/I1=IMOD(WD31_STA,7,'I1');
FYSTA/WMDYY=JAN31_STA-NADJ_STA;

 YEAR_END/YY = 2001 + SEQ;
 JAN01_END/WMDYY=YEAR_END;
 JAN31_END/WMDYY=JAN01_END+33;
 WD31_END/WTR=JAN31_END;
-* NWD31_END/I1=WD31_END;
 NADJ_END/I1=IMOD(WD31_END+1,7,'I1');
 FYEND/WMDYY=JAN31_END-NADJ_END;

END
-RUN
TABLEF FILE CAR
PRINT
 FYSTA
 FYEND
 YEAR_STA
END

March 27, 2012, 11:27 AM

peechy23

This is absolutely great! Thank you so much. One follow up question.

What part of the code is determining how many years it runs? Ultimately I would like to be able to get the start and end dates for 60 years.

March 27, 2012, 11:58 AM

j.gross

quote:
What part of the code is determining how many years it runs?

Nothing explicit - the number of years in the output is the number of data instances (of MODEL) in the CAR file. (Note the WITH clause in the first Define.)

Choose a data source with a sufficient number of instances, and limit the output to 60 lines with

PRINT ...
WHERE RECORDLIMIT EQ 60 ;