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
LHood1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi Larry,

You could create your own:


Sub LHoodDateFile()
 Dim vFF As Long, vDate As Long, vFile As String
 vFile = "C:\datefile.csv"
 vFF = FreeFile
 Open vFile For Output As #vFF
 For vDate = DateValue("Jan 1, 2004") To DateValue("Dec 31, 2018")
  Print #vFF, Format(vDate, "mm/dd/yyyy") & "," & _
   IIf(Weekday(vDate, vbMonday) > 5, 0, IsHoliday(vDate))
 Next
 Close #vFF
End Sub

Function IsHoliday(ByVal aDate As Long) As Long
 'a list of holidays depends on the company/country/religion/etc,
 ' you can use this function to define it yourself
 IsHoliday = 1
End Function


Matt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
what programming environment are you using?

AW
LHood1Author Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mvidasCommented:
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
LHood1Author Commented:
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?
ozoCommented:
#!/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";
}


LHood1Author Commented:
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
ozoCommented:
*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)
mvidasCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.