برای من راحت است که تاریخ شمسی را در اکسل و برنامه های دیگر به صورت یک عدد هشت رقمی ذخیره کنم. مثلا هشتم بهمن هزار و چهارصد و سه به شکل 14031108 ذخیره می کنم.به این شکل می توان تاریخ شمسی را راحت تر مدیریت کرد چون به عنوان یک عدد هشت رقمی برای اکسل نمایان می شود.
برای اضافه کردن به تاریخ برای طول سال بایستی فرمولی باشد که بتواند هنگام رسیدن روز به 30 یا 31 عدد ماه را یک رقم اضافه کند و بعد از اینکه عدد ماه از 12 عبور کند رقم یکان سال را یکی اضافه کند. فرمولی که در کادر زیر هست این کار را انجام می دهد. اول یک صفحه جدید باز کنید تاریخ دلخواه خود را به صورت هشت رقمی بدون فاصله یا کاراکتر در خانه A1 وارد کنید و در خانه A2 این فرمول را کپی کنید. تاریخ روز بعد اضافه خواهد شد. حالا با کشیدن مربع کوچک گوشه سلول A2 میتوان این سلول که فرمول را دارد ادامه داد…
=IF(RIGHT(A1,2)+1>
IF(MOD(INT(A1/100),100)<=6,31,IF(MOD(INT(A1/100),100)<=11,30,29)),
IF(MOD(INT(A1/100),100)=12,(INT(A1/10000)+1)*10000+101,INT(A1/100+1)*100+1),
A1+1)
این فرمول شش ماه اول را 31 روزه و شش ماه دوم را 30 روزه و اسفند را 29 روزه محاسبه می کند.
توضیح مرحلهبهمرحلهٔ فرمول Excel
این فرمول برای افزایش (increment) یک تاریخ ذخیرهشده به شکل عددی YYYYMMDD در سلول A1 طراحی شده — یعنی تاریخ به صورت یک عدد هشترقمی (یا در برخی سالها/نمونهها عدد 7 رقمی اگر صفرهای پیشرو حذف شده باشند) است. هدف: یک روز به تاریخ اضافه کند و در انتهای هر ماه ماه/سال را درست افزایش دهد. حال قدمبهقدم بررسی میکنیم چه اتفاقی میافتد.
۱) قسمتهای اصلی و توابع مورد استفاده
RIGHT(A1,2) → دو کاراکتر آخر متن/عدد در A1؛ روز (DD). مقدار خروجی متنی است ولی هنگام عملگر +1 اکسل آن را به عدد تبدیل میکند.INT(A1/100) → حذف دو رقم آخر (روز)؛ خروجی = YYYYMM به صورت عدد (مثلاً از 20230915 → 202309).MOD(…,100) → با گرفتن MOD(INT(A1/100),100) عملاً دو رقم آخر YYYYMM را جدا میکند — یعنی ماه (MM).- توابع
IF(condition, value_if_true, value_if_false) برای تصمیمگیری استفاده شدهاند.
۲) منطق تعیین تعداد روزهای آخر هر ماه (بخش داخلی IF)
این بخش تعداد روزهای مجاز ماه جاری را برمیگرداند:
IF(MOD(INT(A1/100),100)<=6, 31,
IF(MOD(INT(A1/100),100)<=11, 30, 29))
توضیح:
- اگر ماه (
MM) ≤ 6 → ماههای 1 تا 6: 31 روز.
- وگرنه اگر ماه ≤ 11 → ماههای 7 تا 11: 30 روز.
- در غیر این صورت (ماه = 12) → مقدار 29 برگردانده میشود.
(این الگو دقیقاً با تقویم شمسی/جلالی مطابقت دارد: 6 ماه اول 31 روز، ماههای 7–11 هر کدام 30، ماه 12 معمولاً 29 که در سال کبیسه 30 میشود — نکته مهم: فرمول حاضر بررسی کبیسه بودن سال را انجام نمیدهد و همیشه 29 در نظر میگیرد.)
۳) شرط اصلی: آیا روز + 1 از آخرین روز ماه بزرگتر است؟
بخش بیرونی IF:
IF(RIGHT(A1,2)+1 > (maxDayOfMonth), then_rollover, A1+1)
RIGHT(A1,2)+1 → روز فعلی بهاضافهٔ ۱ (مثلاً 31+1 = 32).- اگر این عدد بزرگتر از
maxDayOfMonth باشد، یعنی باید به روز اول ماه بعد برویم (rollover).
- وگرنه فقط عدد تاریخ را بهسادگی
A1 + 1 افزایش میدهد (مثلاً از 20230914 → 20230915).
نکته: A1+1 در قالب عدد YYYYMMDD کار میکند چون افزایش روز درون همان ماه (مثلاً 14→15) دقیقاً با +1 عددی برابر است.
۴) اگر نیاز به تغییر ماه/سال باشد — بخش then_rollover
بخش then_rollover این است:
IF(MOD(INT(A1/100),100)=12,
(INT(A1/10000)+1)*10000 + 101,
INT(A1/100+1)*100 + 1)
توضیح دو حالت:
حالت (ماه = 12) — پایان سال
MOD(INT(A1/100),100)=12 یعنی اگر ماه جاری دوازدهم باشد:
INT(A1/10000) → عدد سال (YYYY) را جدا میکند (حذف چهار رقم آخر MMDD).(INT(A1/10000)+1)*10000 + 101 → سال را یکی بیشتر میکند، سپس با *10000 و جمع 101 عدد جدید را میسازد:
- نتیجه =
NextYear * 10000 + 0101 → یعنی YYYY+1 01 01 (اول فروردین/ماه 01 روز 01).
- مثال: اگر A1 =
14001229 → INT(A1/10000)=1400 → +1 = 1401 → 1401*10000 + 101 = 14010101.
حالت (ماه ≠ 12) — فقط افزایش ماه
INT(A1/100+1)*100 + 1 — منظور افزایش ماه و تنظیم روز به 01.
- توجه مهم به پرانتزها: آنچه نوشته شده
INT(A1/100+1) معنیاش INT( (A1/100) + 1 ) است. معمولاً مقصود نویسنده این بوده که INT(A1/100) + 1 (یعنی ابتدا YYYYMM را بگیر و سپس یک واحد به آن اضافه کن تا ماه ++ شود). بهصورت عددی این دو اغلب برابرند، اما از نظر خوانایی بهتر است INT(A1/100) + 1 نوشته شود.
- سپس
... *100 + 1 → نتیجه را دوباره به شکل YYYYMM تبدیل میکند و +1 آخر میسازد YYYY (MM+1) 01.
- مثال: اگر A1 =
20230631 (فرض) وقتی ماه 06 و روز 31 روز +1 > 31 → وارد این شاخه میشود:
INT(A1/100) = 202306- +1 →
202307
*100 + 1 → 20230701.
۵) چند مثال عددی (برای فهم بهتر)
- مثال ساده، افزایش روز در همان ماه:
- A1 =
20230914 (14 سپتامبر)
RIGHT(A1,2)+1 = 15maxDayOfMonth (ماه 09 → ماه ≤ 11 و >6) = 30- 15 > 30 ؟ خیر → نتیجه =
A1+1 = 20230915.
- مثال پایان ماه (ماه با 31 روز):
- A1 =
20230131
- روز+1 = 32،
maxDayOfMonth = 31 → 32>31 → باید ماه عوض شود
- ماه = 01 (≠12) → شاخهٔ افزایش ماه → خروجی
20230201.
- مثال پایان سال:
- A1 =
14001229 (در تقویم شمسی؛ روز 29 ماه 12)
- روز+1 = 30،
maxDayOfMonth براساس فرمول = 29 → 30>29 → شاخهٔ ماه=12 برقرار
- سال +1 →
14010101 (اول فروردین سال بعد).
۶) محدودیتها و نکات مهم
- کبیسه (leap year): فرمول بالا هرگز سال کبیسه را در نظر نمیگیرد — ماه 12 همیشه 29 روز فرض شده. اگر لازم است ماه دوازدهم در سالهای کبیسه 30 روز شود، باید شرط اضافی برای تشخیص کبیسهٔ تقویم مورد نظر (جلالی/شمسی) اضافه گردد.
- قالب ورودی: فرمول فرض میکند
A1 یک عدد یا متن با قالب YYYYMMDD است (دو رقم روز همیشه وجود دارد). اگر روز/ماه یک رقمی باشند و صفر پیشرو حذف شده باشد، تفکیک اشتباه میشود.
- خوانایی و یکسانسازی: بهتر است بهجای
INT(A1/100+1) از INT(A1/100)+1 استفاده شود تا دقیقاً منظور «افزایش YYYYMM» روشن باشد.
- نوع داده:
RIGHT خروجی متن میدهد ولی چون بعداً با +1 استفاده شده اکسل آن را به عدد تبدیل میکند — این رفتار معمولاً بدون اشکال است ولی بهتر است برای خوانایی از VALUE(RIGHT(A1,2)) استفاده کرد.
- مثالهای تقویم: الگوی روزها (6 ماه اول 31 روز و…) نشان میدهد فرمول برای تقویم شمسی/جلالی نوشته شده، نه تقویم میلادی (گرگوری). لذا در صورت استفاده برای تقویم میلادی باید منطق ماهها و بهخصوص ماه 2 (فوریه) و کبیسه را تغییر داد.
۷) پیشنهاد بهبود (نمونهٔ بازنویسی خواناتر)
برای خوانایی و ایمنی بیشتر، میتوانید فرمول را بازنویسی کنید مثلاً:
=IF(VALUE(RIGHT(A1,2))+1 >
IF(MOD(INT(A1/100),100)<=6,31,IF(MOD(INT(A1/100),100)<=11,30,29)),
IF(MOD(INT(A1/100),100)=12,
(INT(A1/10000)+1)*10000 + 101,
(INT(A1/100)+1)*100 + 1),
A1+1)
تفاوت مهم: در شاخهٔ افزایش ماه از (INT(A1/100)+1) استفاده شده که خواناتر و مطمئنتر است.
