Can I get help adding to a formula in a Date Formula field?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm working on updating a Date Formula, which tracks the date a report is due based off of an Application Due Date field. Currently, if an application is awarded and I input 1-1-2015 into the Application Due Date field, the Date Formula field (i.e. when a report is due) will return 7-1-2015, which is 6 months later.  The below is what's currently used:

If ([Selected/Awarded] = true,

If ([Application Due Date]=Date(2013, 8, 31), Date(2014, 5, 19),

If ([Application Due Date]=Date(2013, 10, 15), Date(2014, 5, 19),

If ([Application Due Date]=Date(2014, 1, 1), Date(2014, 5, 19),

If ([Application Due Date]=Date(2014, 5, 1), Date(2014, 11, 15), AdjustMonth([Application Due Date], 6))))),

null)

I now need the ability to be more flexible with the report dates, i.e., it's not necessarily 6 month increments anymore.  So that I don't have to erase the formula and go back and manually input all the old report dates and new dates going forward, I would like to try to add to/modify this formula.

Ultimately what I'm hoping for is that any files in the database that have Application Due Dates prior to 5-1-2015 would still use the above formula to determine when a report is due, while any applications that have Application Due Dates on/after 5-1-2015 would be manual text inputs into a Date field by me since the 6 month formula isn't valid anymore.  

Make sense?  Is it even possible?

Photo of David

David

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
One way to do this is using 2 fields:

- [Report Due Date - Formula] - a Formula Date field (we'll modify the existing one you have)

- [Report Due Date - Entered] - a Date field that you enter manually

For the [Report Due Date - Formula] - use this formula:

If ([Selected/Awarded] = true and [Application Due Date] < Date(2015, 5, 1),

Case ( [Application Due Date],

Date(2013, 8, 31), Date(2014, 5, 19),

Date(2013, 10, 15), Date(2014, 5, 19),

Date(2014, 1, 1), Date(2014, 5, 19),

Date(2014, 5, 1), Date(2014, 11, 15), 

AdjustMonth([Application Due Date], 6)

),

[Report Due Date - Entered])



So if [Selected/Awarded] is checked, and [Application Due Date] is before 5/1/2015, then it uses the previous formula.  I used the Case statement to make that part a little simpler.  Otherwise it looks at the manually entered [Report Due Date - Entered].

You can use dynamic form rules to hide [Report Due Date - Entered] if [Application Due Date] is before 5/1/2015, or fill in a certain default value for [Report Due Date - Entered], etc.
Photo of David

David

  • 0 Points
Different but related question:

Is there a way to change the current "Report Due Date-Formula" field to a manual input "Report Due Date-Entered" while simultaneously retaining the date that is currently populated by the formula in the field?

So if the Date-Formula field has a current output of 05-01-2016, is there a way to change the formula field to a manual input field while maintaining 05-01-2016?
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
If you're asking if a Formula Date field can dynamically change to be a manual input Date field, the answer is no.

You'll still have 2 separate fields:  [Report Due Date - Formula] and [Report Due Date - Entered].

You can go to settings and change the type of [Report Due Date - Formula] to be a Date field, but that's a "permanent" change, and it won't be a formula field anymore.

You can use form rules to show or hide one field or the other, depending on what you want - and that could work, if what you're going for is to either show the formula field, or the manual input field.