3

I need to set a Date field on an object to the organization's Fiscal Year's Start Date. This could be a formula field, but ideally it would be just a Date field so that the value could be modified if needed.

I could do this using an Apex trigger, but this object is being used in an Analytic Snapshot, so the object cannot have an after insert trigger.

Is there some sort of hidden DATE() formula function value that I could use to pull out the FISCAL YEAR's start date in the field's Default value function?

  • Guessing a before insert trigger is no good either? – Matt Lacey Nov 19 '12 at 13:27
  • You would have to hard code your fiscal year logic in the forumla itself. When does the fiscal year begin in your company? – Scott VonSchilling Nov 19 '12 at 14:31
  • $Organization.FiscalYearStartMonth is not a valid formula field so I doubt you can do anything... Hardcode the default value in the field definition? Write some scheduled batch job? – eyescream Nov 19 '12 at 14:56
  • No, before insert and after insert, as well as workflow, are both off the table. @eyescream, Scheduled jobs are my best idea right now as well. – zachelrath Nov 19 '12 at 16:19
  • 1
    I think both @ScottVonSchilling and I mean hardcoding it not really as formula field. Rather as setting default value in Date field to DATE(1,1,YEAR(TODAY())) or something that suits your data... You can always later run some data fixes on it (if you'd go with formula field in future it could outgrow 5K char limit and then you'd be in trouble). – eyescream Nov 19 '12 at 16:49
0

For default values you only have one reference date formula, TODAY(), so there isn't a direct route to determine what your current fiscal year settings are. However, you can create the illusion of one by hardcoding the start are end of you're fiscal years. It'll fail eventually, but then you'll probably have a salesforce dev at your disposal who can write the trigger for you.

So if you have the following fiscal years

  • FY2014: 4/1/14 to 3/31/15
  • FY2015: 4/1/15 to 3/31/16
  • FY2016: 4/1/16 to 3/31/17

You could use this formula

IF(TODAY() < DATE(2015,4,1), DATE(2014,4,1),
    IF(TODAY() < DATE(2016,4,1), DATE(2015,4,1),
        IF(TODAY() < DATE(2017,4,1), DATE(2016,4,1),
            ...
        )
    )
)
| improve this answer | |
1

Assuming Fiscal Year doesn't change that often in your company this should work:

TEXT(
IF(TODAY() < DATE(YEAR(TODAY()),7,1)
,YEAR(TODAY())
,YEAR(TODAY())+1)
)

This assumes FY starts on July 1st. So if today is 06/30/2016 your FY is 2016 if today is 07/01/2016 your FY is 2017. You could change the 7 for any other month number your FY starts in. If you need the full date you will need to change it to:

TEXT(
IF(TODAY() < DATE(YEAR(TODAY()),7,1)
,DATE(YEAR(TODAY()),7,1))
,DATE(YEAR(TODAY())+1,7,1))
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.