excelexcel-formulafarsipersian-calendarexcel-dates

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


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: enter image description here

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:

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


Solution

  • 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.