احتمالاً می دانید که می توانید از تابع WEEKNUM برای برگرداندن شماره هفته در یک سال برای یک تاریخ معین استفاده کنید. اگر بخواهید برعکس این کار را انجام دهید - برای تعیین آخرین روز یک هفته خاص اگر تنها چیزی که دارید شماره هفته در سال باشد، چه؟ به عنوان مثال، اگر هفته 37 را مشخص کنید، می خواهید تاریخ آخرین روز را در آن هفته مشخص کنید.
هیچ عملکرد داخلی برای تعیین تاریخ مورد نظر وجود ندارد، اما چند راه وجود دارد که می توانید به مشکل نزدیک شوید و آن را کشف کنید. در این مثال ها فرض می کنیم که سال در سلول A1 و عدد هفته مورد نظر در سلول B1 است.
روش اول این است که اولین روز سال مورد نظر را محاسبه کنید، برای هر هفته هفت روز اضافه کنید و سپس تعداد روزهای هفته را برای اولین روز سال کم کنید.
=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))
این فرمول تاریخی را برمی گرداند که همیشه آخرین روز هفته را نشان می دهد، اگر هفته در یک شنبه به پایان برسد. اگر می خواهید هفته در روز دیگری از هفته به پایان برسد، فرمول پیچیده تر می شود. اگر می خواهید هفته در یکشنبه به پایان برسد، کافی است 1 را به فرمول اضافه کنید:
=DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))+1
با اعمال همین منطق، ممکن است فکر کنید که می توانید هفته های پایانی جمعه را با کم کردن 1 از فرمول، بفهمید. از آنجایی که شما از ابتدای سال به عنوان پایگاه خود استفاده می کنید، اینطور نیست. اگر 1 را کم کنید، با این مشکل مواجه می شوید که در آن، 1 ژانویه هر سال، شنبه است. اگر 1 را کم کنید، به اولین جمعه سال نمی رسید، بلکه به 31 دسامبر سال قبل ختم می شود. برای محاسبه هفته های منتهی به جمعه به فرمول بسیار پیچیده تری نیاز دارید:
=DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))+
((6-WEEKDAY(DATE(A1,1,1))
از آنجایی که برخی از سال ها 52 هفته دارند و برخی 53 هفته دارند - باز هم بسته به اینکه هفته ها به جمعه، شنبه یا یکشنبه ختم می شوند - ایده خوبی است که فرمول ها را طوری تغییر دهید که بررسی کنند آیا تاریخ بازگشت در همان سال است یا خیر. که شما در حال تجزیه و تحلیل هستید. اگر این را بررسی نکنید، فرمول های ارائه شده تا کنون با خوشحالی تاریخ های هفته 73، هفته 89، یا هفته 123 هر سال معین را نشان می دهند - آنها به سادگی تاریخ را در سال آینده مناسب تنظیم می کنند.
فرمول هفته های منتهی به جمعه در اینجا آمده است:
=IF(YEAR(DATE(A1,1,6-WEEKDAY(DATE(A1,1,1))+
((6-WEEKDAY(DATE(A1,1,1))
If the date calculated is not in the same year as what is specified in cell A1, then the formula returns nothing. Here is the formula for weeks ending in Saturday:
=IF(YEAR(DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1)))=A1,
DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1)),"")
Finally, here is the formula for weeks ending in Sunday:
=IF(YEAR(DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))+1)=A1,
DATE(A1,1,1)+B1*7-WEEKDAY(DATE(A1,1,1))+1,"")
