Link to home
Start Free TrialLog in
Avatar of LHood1
LHood1

asked on

Where can I get a flat txt file with a perpetual list of future calendar dates

Im trying to create a way to count number of business days between dates. The problem is I need to find a csv or text file with a list of these dates. Does anyone know where I can find this or how can I do this without manually creating it in excel?
I need the file to contain a list from Jan 1 2004 to Dec 31 2018
I need 2 columns date, type,
type = 0(weekends), 1(businessday), 2(holiday)

LH
ASKER CERTIFIED SOLUTION
Avatar of mvidas
mvidas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what programming environment are you using?

AW
Avatar of LHood1
LHood1

ASKER

Well Im in a  MS Visual Studio.net environment but I guess I could use the vbscript  provided by Matt to accomplish this.. If you have any other suggestions please advise otherwise im going to try Matts code and let you know how it turns out
LH
Unfortunately I know almost no .net, but I'm sure Arthur or someone else would.  I posted that since I've helped you with vba/vb6 before, you could just paste it into excel vba.  But note that there will be no holiday type codes until the IsHoliday function is modified
Avatar of LHood1

ASKER

ok thats fine. so in terms of modifying the type codes I simply will need to add the following dates as holidays?
2004 holidays:
Jan 1 & 2    (new years)
Feb. 16      (presidents day)
May 31       (memorial day)
July 5         (Independents day)
Sep 6          (labor day)
Nov 25 & 26  (thanksgiving)
Dec. 24       (xmas)
Dec. 31      (new yrs eve)

2005 holidays:  same as 2011
Jan 3
Feb 21
May 30
July 1
July 4
Sep 5
Nov 24 & 25
Dec 26

2006 holidays: same as 2017
Jan 2
Feb 20
May 29
July 3 & 4
Sep 4
Nov 23 & 24
Dec 22
Dec 25

The above is the only holiday data I could get. Also I listed which future years match calendarwise. I dont have any other holiday dates for future years just 04,05 & 06.

Special Notes:
1. If new years falls on a weekend then we get following monday off.
2. Presidents day is 3rd monday in Feb
3. Memorial day is the last monday in may
4. If independence day falls on monday we get previous friday off if it falls on weekday then we get day before  july 4th off as well. so that would be july 3 & 4th off for holiday that month.
5. Labor day is first monday in Sep
6. Thanksgiving is the fourth thursday in november we get thursday & friday off
7. xmas dec 25  if on monday we get previous friday off. if during normal weekday we always get dec 24th off.

So how would i function this?
#!/usr/bin/perl
use Date::Manip;
$from=ParseDate("Jan 1 2004");
$to=ParseDate("Dec 31 2018");
for( $date=$from; Date_Cmp($date,$to)<=0; $date=Date_GetNext($date,undef,0,0) ){
    print UnixDate($date,"%b %d %Y, "), UnixDate($date,"%w")>5?0:Date_IsWorkDay($date)?1:2, "\n";
}


Avatar of LHood1

ASKER

Thanks Matt.. if you can help me out on the function it would greatly be appreciated but for now what you have provided so far works great!

LH
*Holiday
1/1                             = New Year's Day
Jan 2 = (new years)
third Monday in Feb             = Presidents' Day
1*0:0:0:0:0:0*EASTER            = Easter
1*11:0:11:0:0:0*CWD             = Veteran's Day (observed)
1*0:0:0:0:0:0*EASTER,PD5        = Good Friday
May 31      = (memorial day)
July 5        = (Independents day)
Sep 6         = (labor day)
fourth Thu in Nov               = Thanksgiving
fourth Thu in Nov + 1 day       =
12/25 - 1 business day          =       (xmas)
1*12:0:24:0:0:0*FW1  = Christmas
1*12:0:25:0:0:0*FW1,a = Boxing
Dec. 31      (new yrs eve)
Sorry about the delay, just remembered this. I got the notif too late last night to do anything about it.
What happens when july 4th is on a weekend? Do you only get that Friday off? Likewise with Christmas..

I did write your function for you, though to save on processor time I determined the holidays beforehand.  I'm hoping someone else here can write a better one for you.  Here is my (ugly) IsHoliday function, works quick though:

Function IsHoliday(ByVal aDate As Long) As Long
 Select Case aDate
  Case #1/1/2004#, #2/16/2004#, #5/31/2004#, #7/2/2004#, #9/6/2004#, #11/25/2004#, #11/26/2004#, #12/24/2004#: IsHoliday = 2
  Case #1/3/2005#, #2/21/2005#, #5/30/2005#, #7/1/2005#, #7/4/2005#, #9/5/2005#, #11/24/2005#, #11/25/2005#, #12/23/2005#: IsHoliday = 2
  Case #1/2/2006#, #2/20/2006#, #5/29/2006#, #7/3/2006#, #7/4/2006#, #9/4/2006#, #11/23/2006#, #11/24/2006#, #12/22/2006#, #12/25/2006#: IsHoliday = 2
  Case #1/1/2007#, #2/19/2007#, #5/28/2007#, #7/3/2007#, #7/4/2007#, #9/3/2007#, #11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#: IsHoliday = 2
  Case #1/1/2008#, #2/18/2008#, #5/26/2008#, #7/3/2008#, #7/4/2008#, #9/1/2008#, #11/27/2008#, #11/28/2008#, #12/24/2008#, #12/25/2008#: IsHoliday = 2
  Case #1/1/2009#, #2/16/2009#, #5/25/2009#, #7/3/2009#, #9/7/2009#, #11/26/2009#, #11/27/2009#, #12/24/2009#, #12/25/2009#: IsHoliday = 2
  Case #1/1/2010#, #2/15/2010#, #5/31/2010#, #7/2/2010#, #9/6/2010#, #11/25/2010#, #11/26/2010#, #12/24/2010#: IsHoliday = 2
  Case #1/3/2011#, #2/21/2011#, #5/30/2011#, #7/1/2011#, #7/4/2011#, #9/5/2011#, #11/24/2011#, #11/25/2011#, #12/23/2011#: IsHoliday = 2
  Case #1/2/2012#, #2/20/2012#, #5/28/2012#, #7/3/2012#, #7/4/2012#, #9/3/2012#, #11/22/2012#, #11/23/2012#, #12/24/2012#, #12/25/2012#: IsHoliday = 2
  Case #1/1/2013#, #2/18/2013#, #5/27/2013#, #7/3/2013#, #7/4/2013#, #9/2/2013#, #11/28/2013#, #11/29/2013#, #12/24/2013#, #12/25/2013#: IsHoliday = 2
  Case #1/1/2014#, #2/17/2014#, #5/26/2014#, #7/3/2014#, #7/4/2014#, #9/1/2014#, #11/27/2014#, #11/28/2014#, #12/24/2014#, #12/25/2014#: IsHoliday = 2
  Case #1/1/2015#, #2/16/2015#, #5/25/2015#, #7/3/2015#, #9/7/2015#, #11/26/2015#, #11/27/2015#, #12/24/2015#, #12/25/2015#: IsHoliday = 2
  Case #1/1/2016#, #2/15/2016#, #5/30/2016#, #7/1/2016#, #7/4/2016#, #9/5/2016#, #11/24/2016#, #11/25/2016#, #12/23/2016#: IsHoliday = 2
  Case #1/2/2017#, #2/20/2017#, #5/29/2017#, #7/3/2017#, #7/4/2017#, #9/4/2017#, #11/23/2017#, #11/24/2017#, #12/22/2017#, #12/25/2017#: IsHoliday = 2
  Case #1/1/2018#, #2/19/2018#, #5/28/2018#, #7/3/2018#, #7/4/2018#, #9/3/2018#, #11/22/2018#, #11/23/2018#, #12/24/2018#, #12/25/2018#: IsHoliday = 2
  Case Else: IsHoliday = 1
 End Select
End Function


Heres a function to create the above Select Case statement.  This way if you need to amend a holiday rule or anything it might be easier for you to see how I did it:

Sub LHHolidays()
 Dim Yr As Long, i As Long, vFF As Long
 vFF = FreeFile
 Open "C:\hol.txt" For Output As #vFF
 Print #vFF, " Select Case aDate"
 For Yr = 2004 To 2018
  Print #vFF, "  Case ";

'1. If new years falls on a weekend then we get following monday off.
  i = Weekday(DateValue("jan 1, " & CStr(Yr)), vbMonday)
  If i < 6 Then
   Print #vFF, "#" & Format(DateValue("jan 1, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
  Else
   Print #vFF, "#" & Format(DateValue("jan 1, " & CStr(Yr)) + Abs(i - 8), "mm/dd/yyyy") & "#,";
  End If
 
'2. Presidents day is 3rd monday in Feb
  i = Weekday(DateValue("feb 1, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("feb 1, " & CStr(Yr)) - i + 21, "mm/dd/yyyy") & "#,";

'3. Memorial day is the last monday in may
  i = Weekday(DateValue("may 31, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("may 31, " & CStr(Yr)) - (i Mod 7), "mm/dd/yyyy") & "#,";

'4. If independence day falls on monday we get previous friday off if it falls on weekday
' then we get day before  july 4th off as well. so that would be july 3 & 4th off for
' holiday that month.
  i = Weekday(DateValue("july 4, " & CStr(Yr)), vbMonday)
  Select Case i
   Case 1
    Print #vFF, "#" & Format(DateValue("july 1, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("july 4, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 2 To 5
    Print #vFF, "#" & Format(DateValue("july 3, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("july 4, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 6    'you didnt say what happens when july 4th is weekend, I'm guessing just friday
    Print #vFF, "#" & Format(DateValue("july 3, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 7
    Print #vFF, "#" & Format(DateValue("july 2, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
  End Select

'5. Labor day is first monday in Sep
  i = Weekday(DateValue("sep 1, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("sep 1, " & CStr(Yr)) + 7 - i, "mm/dd/yyyy") & "#,";
 
'6. Thanksgiving is the fourth thursday in november we get thursday & friday off
  i = Weekday(DateValue("nov 1, " & CStr(Yr)), vbFriday)
  Print #vFF, "#" & Format(DateValue("nov 1, " & CStr(Yr)) - i + 28, "mm/dd/yyyy") & "#,";
  Print #vFF, "#" & Format(DateValue("nov 1, " & CStr(Yr)) - i + 29, "mm/dd/yyyy") & "#,";

'7. xmas dec 25  if on monday we get previous friday off. if during normal weekday we
' always get dec 24th off.
  i = Weekday(DateValue("dec 25, " & CStr(Yr)), vbMonday)
  Select Case i
   Case 1
    Print #vFF, "#" & Format(DateValue("dec 22, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("dec 25, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 2 To 5
    Print #vFF, "#" & Format(DateValue("dec 24, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("dec 25, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 6    'you didnt say what happens when xmas is on a weekend, I'm guessing just friday like jul 4
    Print #vFF, "#" & Format(DateValue("dec 24, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 7
    Print #vFF, "#" & Format(DateValue("dec 23, " & CStr(Yr)), "mm/dd/yyyy") & "#";
  End Select
 
  Print #vFF, ": IsHoliday = 2"
 Next
 Print #vFF, "  Case Else: IsHoliday = 1"
 Print #vFF, " End Select";
 Close #vFF
End Sub

Matt