by
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
If you can think of other formulas please share below.
Why we can't use this formula instead =text(A2,"DDD")
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.
Helpful video, good stuff!
Hi Leila, I'm looking to use the WeekNum function in a UDF in VBA, but I can't get it to work, any Ideas?
For stuff like this, can you convert the range into tables and show how to use formulas using table notation?
Helps me a lot! Can you explain how to make 2 digit week number, like 01, 02 etc?
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
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().
I want to make sum by week and rest every month
Superb work. Magnificent.
explanation is bad
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
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!!
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.
Very well explained Leila. Thanks for sharing.
awesome thank you Ms. Leila easy to understand as always
very informative. Thanks on behalf of all Excel learners/users/addicts.
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.
Very Awesome vedio. The tricks are very unique and very easy to understand. Thanks for uploading this vedio.
Thanks for the WEEK-Num-Day formulas : )
Very Interesting 🙂
Very informative LG
THANKS TO SHARE