Converting Dates to Fiscal Dates

[nerdgir1]

I'm generating a report where I'm Summarizing data by Month/Year. The problem I'm having is the dates need to correspond to our company's fiscal calendar. So something with a date 7/1/2004 would need to go under the month/year 06/2004 (since according to our Fiscal calendar, that item happened in "fiscal" June).

Does anyone have any ideas for how to create a calculation field that performs this conversion?

BTW I noticed a function in version 6 called "WeekofYearFiscal", would that be useful in this instance?

[Lee Smith]

HI nerdgir1

Welcome to the list.

This caluculation will calculate out what fiscal year a date is in.

If(Month(Date) > 6, Year(Date) & "-" & (Year(Date)+1),(Year(Date)-1) & "-" & Year(Date))

HTH

Lee

[nerdgir1]

Thanks for the response, it gave me something to think about. I don't need a range, rather a conversion to a specific date. BTW on my calendar, the Fiscal Week starts on Saturday.

Basically what I'm looking for is to convert this: Date = 07/02/2004

to this: Extract MonthYear Fiscal = 06/2004

(note: I already have a function to extract the Month/Year to my liking, I just need to know what conditions to add to account for the Fiscal adjustment).

I can set it up as a hard coded "Case" statement, but I was really hoping for something more elegant and dynamic. If anyone has any ideas I'm down to hear them.

Like for instance, what are the rules for a Fiscal date? I don't even know how they come up with that stuff... like if there's some generic conversion algorithm for normal dates to fiscal.

[nerdgir1]

I just went ahead and coded a Case Statement. It was the simplest solution.

Case(Date Closed ? TextToDate("12/1/2001") and Date Closed ? TextToDate("12/28/2001"), TextToDate("12/1/2001"), 0)