Ensure Excel (Persian) date is valid date with specific format

Question

Problem: We a need a specific column to accept a (Persian) date with specific format (yyyy/mm/dd).

Attempts: I've tried to use Data Validation, to a accept specific format:

Blocker: The date we're writing is a Persian date in 'yyyy/mm/dd' format, this is why the excel data validation method will not work, because the minimum year in 1900 and Persian years are in 1300s and 1400s.

An Example of a Persian date with desired format:

• Persian (Jalili) date: 1347/07/01
• Gregorian equivalent: 1968/09/23

I have tried to the formula below as a custom data validation but I guess it did not work:

Answer 1

In order to check the format, you must evaluate the entry as a text string, not as a date. One way of doing this, with your data entry cell being E2 (if I understand the calendar correctly, would be with this formula (requires Office 365 with TEXTSPLIT and LET functions):

Edited to correct logic for 31/30 day months

=LET(s,TEXTSPLIT(E2,"/"),
y,AND(--INDEX(s,1)>=1278,--INDEX(s,1)<=9378),
m, AND(--INDEX(s,2)>=1, --INDEX(s,2)<=12),
d, AND(--INDEX(s,3)>=1, --INDEX(s,3)<=(30+(--INDEX(s,2)<=6))),
AND(y,m,d))

Alternatively, you could create a user form for data entry, where the YMD are entered in separate boxes, and those boxes are restricted to the valid ranges.

Answer 2

I think it's possible if you're prepared to use helper cells for the upper and lower limits of your valid-date input. In the screenshot below

the cells B7:B8 have been formatted for the Date type, according to the Persian locale.

The cell A4, containing the user's input, has the following custom number format

and the following data-validation rule