Start a new topic

RegEx for calculating fiscal year?

Ugh. My first attempt to post this was apparently eaten by Safari. Thanks, Apple.


I am looking for a way to convert a date to another date, conditionally. 


Basically, if the date is before July 1, I want to convert it to June 30 of the current year. 


If it's after July 1, I want to convert it to June 30 of the NEXT year.


Does anyone have any guidance on this?

Thanks!

Christopher Hatty


This would be pretty hard (maybe impossible?) to do in a dictionary. Regex doesn't think of data as "dates", just as individual characters. You can't just "add 1" to something unfortunately.

The best bet here is to make a custom function with the API. The logic I use to figure out the FY is to add 6 months to the date and then look at the year.
ex.
Jan 1 2014 + 6 months = Jun 1 2014 (FY 2014)
Aug 1 2014 + 6 months = Feb 1 2015 (FY 2015)
Jun 30 2014 + 6 months = Dec 31 2014 (FY 2014)
Jul 1 2014 + 6 months = Jan 1 2015 (FY 2015)

With VB you can add 6 months to a date using: <b>DateAdd("m", 6, dInputDate)</b>
To get the year you can do: <b>Year(DateAdd("m", 6, dInputDate))</b>

dInputDate is whatever date you are looking at, if this is a custom function then oField.Value would be the date of the field where you apply the function. Therefore the function might look something like:


[code] <ImportOM.API.VirtualColumn("SetLastFYDate")> _ Public Function ComputedColumn_635404310172334293( _ ByVal oField As ImportOM.API.iField, _ ByVal Cancel As ImportOM.API.iCancel) As String 'This function must return a text value Dim sReturn As String = "" 'Declare your variables Dim dInputDate As Date Dim iFY As Integer ' Get the input date dInputDate = oField.Value 'Add 6 months to the input date and then take just the year part iFY = Year(DateAdd("m", 6, dInputDate)) ' Create the new date by adding the year to 6/30 sReturn = "6/30/" & iFY ' Return the new date to replace the value in the field Return sReturn End Function [/code]
The code tags converted my "&" into "&"

So
sReturn = "6/30/" & iFY
Should Be
sReturn = "6/30/" & iFY
Thanks! I will look into this once I get a few simpler problems solved!
In case you want it here is the formula to do the same thing in Excel.

Assuming your date is in A1:
=DATE(YEAR(DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))),6,30)
Login or Signup to post a comment