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:

I don't know that you can enter Persian dates directly into Excel. Especially if you want it in a given format, you will need to validate it as a text string. Alternatively, create a user form where you select year/month/day from dropdowns which limit the possible inputs to only valid ones. — Ron Rosenfeld, Jan 30 '23 at 12:24

Ron Rosenfeld · Accepted Answer · 2023-02-01T21:38:29.050

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.

edited Feb 01 '23 at 21:38

answered Jan 30 '23 at 12:50

Ron Rosenfeld

53,870
7
28
60

Thanks for amazing solution! .. One thing, I've changed the year to 31+(m<=6) since the 1st 6 months are 31 days and the next 6 month are 30 days. – Mohamed Mostafa El-Sayyad Feb 01 '23 at 15:10
1

@MohamedMostafaEl-Sayyad Turns out both my original and your correction are **wrong**. Please use the corrected version in my edit. That part should be changed to: `30+(--INDEX(s,2)<=6)` – Ron Rosenfeld Feb 01 '23 at 21:41
Thanks! It works in excel perfect validating the suggested format. I see the dynamic way you did it. One thing is that it prompts me an error, I click ok and it works. https://imgur.com/a/qSeeNRz The problem is that I'm generating your formula using python's XlsxWriter library, it puts the formula indeed in the data validation under custom, but it doesn't work. You'd have to re-run it from excel and click ok on error prompt to allow it. Is there a way to remove this error we get, because I think it's blocking from it to work? – Mohamed Mostafa El-Sayyad Feb 03 '23 at 07:53
I've asked about the `xlsxwriter` python library I'm using in thread https://stackoverflow.com/questions/75337445 – Mohamed Mostafa El-Sayyad Feb 03 '23 at 14:59
1

@MohamedMostafaEl-Sayyad I think that is a "feature" of data validation by formula in Excel in general. I've seen it with multiple formulas. I'm not familiar with xlswriter and don't use python, so maybe someone more knowledgeable can help. – Ron Rosenfeld Feb 03 '23 at 15:39
Thanks It was from the python library side, we had to append a prefix for those functions `LET` and `TEXTSPLIT`. Now passing the formula from python works! – Mohamed Mostafa El-Sayyad Feb 06 '23 at 06:24

score 1 · Answer 2 · answered Jan 30 '23 at 18:29

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

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

2 Answers2

Linked