Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculation for current fiscal year


Chatterdada

Recommended Posts

If my fiscal year begins on July 1st of each year and goes to June 31st, how can I get a report that automatically gives me the date range for the CURRENT fiscal year. I would like to do this in a script.

 

Thanks!

Link to comment
Share on other sites

This will find records from the beginning of the current FY to the current date:

 

Case(

Month(Status(CurrentDate)) < 7, DateToText(Date(7, 1, Year(Status(CurrentDate))-1)) & ".." & DateToText(Status(CurrentDate)),

DateToText(Date(7, 1, (Year(Status(CurrentDate))))) & ".." & DateToText(Status(CurrentDate))

)

If you want to allow for dynamic searches from the beginning of the current FY to a user-entered date, create a globalDate field for desired date entry, then:

 

Case(

Month(globalDate) < 7, DateToText(Date(7, 1, Year(globalDate)-1)) & ".." & DateToText(globalDate),

DateToText(Date(7, 1, (Year(globalDate)))) & ".." & DateToText(globalDate)

)

Use these in Insert Calculated Result steps in your scripts, and remember the requirement that the date field you are searching on must be on the active Layout.

Link to comment
Share on other sites

You could also have 2 date fields :

d_FYStart

d_FYEnd,

which values would be auto-entered from 2 other global fields:

g_dFYStart

g_dFYEnd).

Then, you'd update the global and Loop through all records with a yearly script to reset all the previously entered dates to the new global values.

 

This would allow you to have an index boolean calculation of

DateField>=d_FYStart and DateField

 

The only thing you'd need than would be a global number field of 1 at Left side and a relationship from this global to the boolean calc.

Link to comment
Share on other sites

Hello Chatterdada,

Errm... well, for one thing, I think you mean June 30th (there are only 30 days in june - even in a leap year! :eek: ).

 

That aside, I'm guessing you might like to locate the current fiscal year records for your report via a script. If that's the case, you might like to set up something along the lines of:

 

Go to Layout [any Layout which has your existing RecordDate field on it]
Enter Find Mode [ ]
Insert Calculated Result ["RecordDate", "DateToText(Date(7, 1, Year(Status(CurrentDate)) - (Month(Status(CurrentDate)) < 7))) & "..." & DateToText(Date(6, 30, Year(Status(CurrentDate)) + (Month(Status(CurrentDate)) > 6)))"]
Perform Find [Replace Found Set]
Go to Layout [Your Report layout]
...etc

It's compact and clean and requires no additional fields (ie the script will operate by performing its 'find' on the existing date field within your records). smile.gif

 

NB: If records are never entered in advance - ie if the values which reside in the RecordDate field (whatever it may be called) are always equal to or less than the current date, then the latter half of the Insert Calculated Result [] formula could be truncated to DateToText(Status(CurrentDate)) (as in the formulae suggested by Jim), but going by the actual question - as you've worded it - the full form would seem to be necessary... wink.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use