آنیتا یک کاربرگ با بیش از 10000 ردیف دارد. هر ردیف نشان دهنده یک قرارداد فردی است. هر قرارداد دارای یک تاریخ شروع (ستون A) و یک تاریخ پایان (ستون B) است. او به راهی برای فهرست کردن ماه های جداگانه تحت پوشش قرارداد در ستون های C، D، E و غیره نیاز دارد. برای مثال، اگر ستون A شامل 01 ژوئیه 2017 و ستون B شامل 30 سپتامبر 2017 باشد، ستون C باید شامل 17 ژوئیه، ستون D شامل 17 اوت و ستون E شامل 17 سپتامبر باشد. هر فرمول استفاده شده باید مطابق با قرارداد باشد. طول هایی که ممکن است کاملاً متفاوت باشند.
راه های مختلفی برای انجام چنین کاری وجود دارد. می توان از برخی ستون های میانی استفاده کرد که مواردی مانند ماه شروع و تعداد ماه های هر قرارداد را مشخص می کنند، اما معلوم می شود که چنین ستون های میانی ضروری نیستند. به عنوان مثال، با فرض اینکه قراردادهای شما در ردیف 2 شروع می شود، می توانید فرمول زیر را در سلول C2 قرار دهید:
=TEXT(A2,"mmm yy")
سپس، با شروع در سلول D2، می توانید این فرمول را قرار دهید:
=IF(DATE(YEAR($A2),MONTH($A2)+COLUMNS($D2:D2),
DAY($A2))>$B2,"",TEXT(DATE(YEAR($A2),MONTH($A2)
+COLUMNS($D2:D2),DAY($A2)),"mmm yy"))
این یک فرمول است و از موقعیت سلول های حاوی فرمول به همراه تاریخ شروع قرارداد استفاده می کند تا اساساً ماه آفست آن ماه شروع را محاسبه کند. فرمول را می توان به سمت راست (ستون های E، F، G و غیره) تا جایی که لازم است برای نمایش همه ماه ها و سال ها کپی کرد.
یکی از اشکالات فرمول این است که اگر تاریخ پایان قرارداد در ماه زودتر از تاریخ شروع قرارداد باشد، ماه آخر نمایش داده نمی شود. بنابراین، اگر قرارداد از 12 ژوئن 2017 شروع شود و در 05 فوریه 2018 به پایان برسد، ماه آخر (فوریه) با فرمول نمایش داده نمی شود. در عوض، می توانید از فرمول زیر در سلول C2 استفاده کنید و تا آنجا که لازم است آن را به سمت راست کپی کنید:
=IF(DATE(YEAR($A2),MONTH($A2)+COLUMN()-2,0)
>DATE(YEAR($A2),MONTH($B2)+1,0),"",TEXT(DATE
(YEAR($A2),MONTH($A2)+COLUMN()-2,0),"mmm yy"))
این فرمول همچنین بر ستون هایی که در آن قرار دارد متکی است و از آنها برای محاسبه افست از تاریخ شروع قرارداد استفاده می کند. این فرمول بدون در نظر گرفتن رابطه بین تاریخ شروع و پایان قرارداد به خوبی کار خواهد کرد.
اگر نمی خواهید به موقعیت ستون تکیه کنید، رویکرد دیگری وجود دارد که می توانید در پیش بگیرید. فرمول زیر را در سلول C2 قرار دهید:
=(A2)
سپس در سلول D2 فرمول زیر را قرار دهید:
=IF($B2>C2,EOMONTH(C2,1),"")
فرمول موجود در D2 را به تعداد سلول های لازم در سمت راست کپی کنید و سپس تمام آن سلول ها (از جمله C2 و D2) را به عنوان تاریخ هایی که فقط ماه و سال را نشان می دهند، قالب بندی کنید. این فرمول به B2 (تاریخ پایان قرارداد) نگاه می کند تا ببیند آیا بزرگتر از C2 است یا خیر. اگر اینطور باشد، فرمول شماره سریال آخرین روز ماه بعد را به صورت سری برمی گرداند. اگر اینطور نیست، یک خالی ("") برگردانده می شود.
