Excel Formulas and Macros

Fri Mar 2 21:54:34 EST 2007

To put the name of the file in a cell:

=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,FIND("]",CELL ("filename",$A$1),1)-FIND("[",CELL("filename",$A$1),1)-1)

Mon Jan 8 19:29:42 EST 2007

Option Explicit

Function add_workdays(start_d As Date, offset As Integer) As Date
    ' add offset - 1 days to start_d, using only working days
    Dim i As Integer
    ' start date must be a work day
    If (Not workday(start_d)) Then
        add_workdays = #12/27/1954#
        Exit Function
    End If
    
    add_workdays = start_d
    For i = 1 To offset
        add_workdays = add_workdays + 1
        While Not workday(add_workdays)
            add_workdays = add_workdays + 1
        Wend
    Next i
End Function

Function workday(d As Date) As Boolean
    ' test for work days, skipping weekends and holidays
    ' you have to define your own holidays, thoes below
    ' are the ten national US holidays for 2007
    If (Weekday(d) = 1 Or Weekday(d) = 7 Or _
    d = #1/1/2007# Or d = #1/15/2007# Or _
    d = #2/19/2007# Or d = #5/28/2007# Or _
    d = #7/4/2007# Or d = #9/3/2007# Or _
    d = #10/8/2007# Or d = #11/12/2007# Or _
    d = #11/22/2007# Or d = #12/25/2007#) Then
        workday = False
        Exit Function
    End If
    workday = True
End Function

Tue Dec 19 22:56:44 EST 2006

To put the name of the sheet in a cell:

=RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1))

Excel Formulas and Macros

Navigation