SharePoint Date Columns Using Hide Show Conditional Not Working Until After Save

Question

I created a new list, with 2 Date Only columns, and a Single Line Text field that will be dynamically hidden based on the days being more than 30 days apart.

For the Single Line Text field, on the Add New Item screen, I edit the columns in the form, and add this conditional formula for visibility:

=Number([$Date2]) > ((Number([$Date1]) + 2592000000)) //30 days

Now it works, but only when I save the form and it closes and I go back into it. Its not dynamically hiding and showing my Single Line Text field.

Any ideas? I have never had this before. I have other formulas that work no problem.

Answer 1

Hi Ian Marsden

Thank you for reaching out to Microsoft Q&A forum

As far as I know, this behavior occurs because SharePoint’s built-in conditional formulas for column visibility are evaluated only when the form loads or when referenced fields trigger a refresh. Complex calculations using Number() and milliseconds often do not re-render dynamically in the modern form interface.

From my perspective view, you can try these approach methods below to see if it can resolve your problem:

Initially, you can use the DATEDIF function instead of converting dates to milliseconds. This is simpler, natively supported in SharePoint formulas, and avoids potential issues with time zones or precision.

IF(DATEDIF([$Date1], [$Date2], "d") > 30, true, false)

Notes: Ensure Date2 is after Date1 (otherwise, DATEDIF may return errors—add safeguards like AND([$Date2] > [$Date1], DATEDIF([$Date1], [$Date2], "d") > 30) if needed). Test by entering dates and tabbing out; it should re-evaluate on field changes

In the other hand, you can try to use JSON column formatting approach:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "hideColumnHeader": false,
  "additionalRowClass": "=if(@now > [$Date2], 'sp-css-backgroundColor-errorBackground', '')"  // Example: Style row based on date; adapt for your needs
}

In case this behavior still persists after trying above methods, kindly let me know in the comment section.

Best regards

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

It seems that the issue you are experiencing with the visibility of the Single Line Text field in your SharePoint list may be related to how the conditional formulas are evaluated in the form. Conditional formulas in SharePoint forms may not always trigger dynamically as expected, especially when dealing with date calculations.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

1. Formula Validation: Ensure that your formula is correctly structured. The formula you provided seems to be checking if the second date is greater than the first date plus 30 days in milliseconds. Make sure that both [$Date1] and [$Date2] are returning valid date values.
2. Refresh Logic: Sometimes, the form may not refresh the visibility of fields dynamically until after a save action. You might want to check if there are any settings or additional scripts that can force a refresh of the form when the date fields are changed.
3. Browser Cache: Clear your browser cache or try accessing the form in a different browser to rule out any caching issues that might affect how the form behaves.
4. Testing with Simpler Logic: As a test, try using a simpler conditional formula to see if it dynamically shows/hides the field. For example, check if just comparing the two dates without adding the 30 days works:

      =Number([$Date2]) > Number([$Date1])
   

5. Check for Updates: Ensure that your SharePoint environment is up to date, as sometimes issues can be resolved with updates or patches from Microsoft.

If these steps do not resolve the issue, it may be beneficial to consult the SharePoint documentation or support for further assistance.

---

References:

• Show or hide columns in a list or library form
• Customize a form for a SharePoint list
• Column Formatting
• Upgrading from XSLT views rendering to List Formatting
• Formatting syntax reference
• Operators and Identifiers in Power Apps