Anniversaries

It has just come to my notice that this year I missed my half millionth Birthhour, which was also my 30 millionth Birthminute. Luckily I still have plenty of time to prepare for my 2 billionth Birthsecond.

To avoid missing these significant occasions download Anniversaries.zip and calculate to the second the exact anniversary from any specified date and time.

Anniversaries - Screen shot

Anniversaries - Screen shot

Advertisements
This entry was posted in Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

3 Responses to Anniversaries

  1. Nick Partridge says:

    =DateAfter() is new to me (and to excel 2007) I assume it is VBA?

    Like

  2. dougaj4 says:

    Nick – yes there are two UDFs included in the download; =DateAfter() and =Phi().

    Here’s the code:

    Function Phi() As Double
    Phi = 1.61803398874989
    End Function

    Function DateAfter(DateFrom As Double, Period As Double, Optional TUnit As String) As Variant
    Dim TFactor As Double, Yearinc As Long, Monthinc As Long, Dayinc As Double, YearLength As Long

    If IsMissing(TUnit) = True Then
    TFactor = 1
    Else
    TUnit = LCase(TUnit)
    Select Case TUnit
    Case “seconds”
    TFactor = 1# / (24# * 3600#)
    Case “minutes”
    TFactor = 1 / (24 * 60)
    Case “hours”
    TFactor = 1 / (24)
    Case “days”
    TFactor = 1
    Case “weeks”
    TFactor = 7
    Case “months”
    TFactor = -1
    Case “years”
    TFactor = -2
    Case Else
    DateAfter = “Invalid Time Unit”
    Exit Function
    End Select
    End If

    If TFactor = -1 Then
    Yearinc = Int(Period / 12)
    Monthinc = Int(Period – Yearinc * 12)
    Dayinc = (Period – Int(Period)) * 365 / 12
    DateAfter = DateSerial(Year(DateFrom) + Yearinc, Month(DateFrom) + Monthinc, (DateFrom – Int(DateFrom)) + Dayinc)

    ElseIf TFactor = -2 Then
    Yearinc = Int(Period)
    DateAfter = DateSerial(Year(DateFrom) + Yearinc, Month(DateFrom), Day(DateFrom))
    YearLength = DateSerial(Year(DateFrom) + Yearinc + 1, Month(DateFrom), Day(DateFrom)) – DateAfter
    Dayinc = (Period – Int(Period)) * YearLength
    DateAfter = DateAfter + Dayinc + (DateFrom – Int(DateFrom))

    Else
    DateAfter = DateFrom + Period * TFactor

    End If

    End Function

    Like

  3. Pingback: Daily Download 27: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s