UniformSoftware main content

Custom Date Formats in Excel Date Picker

Starting with version 1.3.3, Pop-up Excel Calendar supports "custom date formats", which enable you to specify your own valid and/or invalid date formats. If a custom date format is added to the Valid Date Format list, Pop-up Excel Calendar recognizes it as a valid date format, no matter whether it is a normal date format in Excel; likewise, if a custom date format is added to the Invalid Date Format list, the format will never be seen as a date format, even if Excel itself can apply it.

Let's find out how to use custom date formats with an example.

By default, Pop-up Excel Calendar displays the in-cell icon button for cells formatted with Text, since Excel can format a date value with the Text format. Here is how you can change this default behavior by utilizing custom date formats in Excel / Microsoft 365.

  1. Click the Settings button on the Pop-up Excel Calendar toolbar to display the Settings window.
  2. Click the Custom Date Formats button to open the Custom Date Formats window. As you can see from the figure, the Custom Date Formats window provides two lists: one for valid date formats and another for invalid date formats.

    custom date formats

  3. To find out the format codes of the Text format, click the Format Cells/Number button, which opens the Number tab of the Format Cells dialog box provided by Excel.

    custom date formats - The Number tab of the Format Cells box

  4. In the Category list, choose Text. See the figure above.
  5. Click Custom in the Category list. In the Type section, Excel selects the format code of the Text format automatically. As you can see, the format code of the Text format is "@".

    custom date formats - the format code of Text format

  6. Click OK or Cancel to close the Format Cells dialog box. Closing the dialog box does not modify the format of the current selected cell, no matter which button, OK or Cancel, you click to close the dialog box.
  7. Now on the Custom Date Formats box, click to activate the text box below Invalid date format, enter "@". This is the format code for the Text format.

    custom date formats - Add an invalid date format

  8. Click Add.
  9. Click OK to close the Custom Date Formats dialog box.
  10. Click OK to close the Settings dialog box.
  11. Now if you click a cell formatted with Text, the in-cell icon button won't appear.