September 26, 2020

Excel Reset Week Number Every Month – (WeekDay & WeekNum Functions Explained)



In this video I show you two ways to reset the week number to start from 1 at the beginning of every month.

1st case: We use Excel’s WEEKNUM function to start from 1 every time we reach the 1st of the next month.

2nd case: We reset the week number to 1 every time we reach the first Monday of the next month. Here we use Excel’s WEEKDAY function as the main formula driver.

In addition you will learn how to:

1. Get Week Number from a date with the WEEKNUM formula
2. How to Get the Day name from a date using the WeekDay function
3. How to use the DATE function (Year, Month, Day)

🡻 Download the workbook here:

Links to related videos:
Mike’s Magic Trick 783: Date Functions & Formulas:

Workday function explained:
NetWorkDays explained:
Quick Gantt Chart in Excel:

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

Get Office 365:
Microsoft Surface:

GEAR
Camera:
Screen recorder:
Microphone:
Lights:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://sylvaniachristian.com/

Xem thêm bài viết: https://sylvaniachristian.com/category/cong-nghe

23 thoughts on “Excel Reset Week Number Every Month – (WeekDay & WeekNum Functions Explained)

  1. Hi Leila,
    I am writing to you first time so please forgive me if I am wrong but I found a bug in this formula. Whenever Monday falls on 30 or 31st of the month, it is showing the incorrect week number (starting from 2) in the next month. Please check and provide the feedback.
    Anyway, I am a very huge fan of your teaching style.

  2. Leila , Could you please to help me to Know Formula to count the past weeks passed in the month (i mean Fridays Passed in month till today ) – For Example Today we are 03 Dec 2018 = 0 , if we are 08/12/2018 the Formula count 1 and 22 Dec 2018 its Count 3 , I hope to clear to understand it
    thanks

  3. I've reduced the weeknum process down in an attempt to reduce redundancies. =WEEKNUM(Date)-WEEKNUM(DATE(YEAR(Date),MONTH(Date),1))+IF(WEEKDAY(DATE(YEAR(Date),MONTH(Date),1))=7,0,1). Since the only IF part of the formula was to decide whether or not a 1 is added at the end, I added the IF() function at the end. Let me know if you found a more efficient way to calculate the per week using WEEKNUM().

  4. How to start my counting weeks when the first one is the last week in December from the previous year? like I want to consider the first day is 12/26/2017

  5. Leila, I need some help. Can you please put together a quick formula to show dynamic date headers? I'm attempting to show what this month is based on today() and then what next month will be based on the month of today(). if possible please also show the annual crossover. Thank you in advance!!

  6. Thank you so much Leila, your explanation is very clear to follow… i hope someday you can teach me how to make weekly gantt chart from serials dates. Thank You.

  7. To avoid the If Saturday condition, I've done something like this for WeekNum: =IF(WEEKDAY(B2,2)>5,"",WEEKNUM(B2,16)-WEEKNUM(EOMONTH(B2,-1)+1,16)+1) -> as in starting the week on Saturday, it's not displayed anyway.

Leave a Reply

Your email address will not be published. Required fields are marked *