FY dates independent of year 1

[dcurtis]

I am trying to build a function that will return the "ordinal value" of the day in the fiscal year. I have that part ok, but I don't want to have to change the dates every year. I have tried formatting a date in VBA excluding the year, but have no luck. If I don't specify a year in the date the current year is automatically used. For example if I write:

Format(#12/31#, "y") (to get the ordinal value of 12/31 of the current year

VBA AUTOMATICALLY changes it to Format(#12/31/current year#, "y").

I don't want to have to change the code every year. Is there a way around this?

 

[bluedragon2]

The reason behind that is that excel stores dates as numbers starting with 1 for 1/1/1900 and 38401 for 4/27/2004. Excel does not recognize just day and month for a date value. You can create this ordinal, but it will have to be in a text format. Even in a text format, you can manipulate it with the MONTH and DAY functions.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[TonyJollans]

Hi dcurtis,

It's just the compiler trying to help!

You could use ...

[blue]

Format(DateSerial(Year(Date), 12, 31), "y")

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Golom]

It has to have a year value because the answers are different for leap years than for non-leap years. For example

[blue][tt]
Format(#12/31/04#, "y") = 366
Format(#12/31/03#, "y") = 365
[/tt][/blue]

 

[Bowers74]

Hello dcurtis,

I know that you wrote that you are writing a function, yourself, but I read this post and though I might throw you a line. I wrote the following VBA functions for Excel about 18 months ago, and they have been working fine for me at work.

Use them if you like, or maybe they might give you some ideas for your function:

Here is a function to calculate the day in the fiscal year:

Function FYDay(FYDate As Long, FYStartMonth As Long) As Long
Dim FYFirstDate As Long, d As Long, dCount As Long
If Month(FYDate) < FYStartMonth Then
    FYFirstDate = DateSerial(Year(FYDate) - 1, FYStartMonth, 1)
Else
    FYFirstDate = DateSerial(Year(FYDate), FYStartMonth, 1)
End If
If FYDate < FYFirstDate Then
        FYDay = CVErr(xlErrValue)
ElseIf Month(FYDate) >= Month(FYFirstDate) And _
    Year(FYDate) > Year(FYFirstDate) Then
        FYDay = CVErr(xlErrValue)
Else
        For d = FYFirstDate To FYDate
            dCount = dCount + 1
        Next d
        FYDay = dCount
End If
End Function

Example: Cell A1 contains 8/15/2005, in Cell A2 you enter =FYDay(A1,10). The formula returns 319.

The following function gives you the Quarter of the fiscal year:

Function FYQtr(FYDate As Long, FYStartMonth As Integer) As Integer
Select Case FYStartMonth
    Case Is = 1 ' FY starts in January
        FYQtr = Int((Month(FYDate) - 1) / 3 + 1)
    Case Is = 4 ' FY starts in April
        If Month(FYDate) >= 4 Then
            FYQtr = Int((Month(FYDate) - 1) / 3)
        Else
            FYQtr = 4
        End If
    Case Is = 7 ' FY starts in July
        If Month(FYDate) >= 7 Then
            FYQtr = Int((Month(FYDate) - 1) / 3 - 1)
        Else
            FYQtr = Int((Month(FYDate) - 1) / 3 + 3)
        End If
    Case Is = 10 ' FY starts in October
        If Month(FYDate) >= 10 Then
            FYQtr = 1
        Else
            FYQtr = Int((Month(FYDate) - 1) / 3 + 2)
        End If
    Case Else
        FYQtr = CVErr(xlErrValue)
End Select
End Function

Example: Cell A1 contains 6/15/2005, in Cell A2 you enter =FYQtr(A1,10). The formula returns 3.

I hope these help!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[dcurtis]

Thanks Tony. Exactly what I was looking for.