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

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)

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]