So sReturn = "6/30/" & iFY Should Be sReturn = "6/30/" & iFY
C
Christopher Hatty
said
over 8 years ago
Thanks! I will look into this once I get a few simpler problems solved!
W
Wayne Pozzar
said
over 8 years ago
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)
W
Wayne Pozzar
said
over 8 years ago
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]
Christopher Hatty
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