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.
You're welcome. I have a few Gantt chart videos here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QZd3iXcueaI.html for a quick gantt chart and this one ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KtR-CVxC5qA.html for a fully featured gantt chart.
Leila Gharani thank you for the link.. is helping alot... my other issue is can u teach me how to make start and end date in every week but exclude for weekend. I am gonna make group column by week and showing start and date of week in every months. I hope you can understand what i mean. Sorry for my poor english. Thank you so much.
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.
This is really helpful,You did a good job explaining bit by bit but I have tried applying the formulas but it doesn't come out correctly in my excel 365. I have used adis(comment) formula and it worked for me but I'm still trying to figure it out how is that method come out like that. Will you refer me a video that could explain his(adis) formula?
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.
I like this!!! Much shorter than mine. You're right - it's much better to have the cut-off on Saturday. Thanks for this - I will add to the download file if you don't mind.
@@AdiCristea hi adi, could you help me with your formula... when i use your formula on current year, the second week of march goes to 7th not 8th and in april goes to 4th not the 8th... can you help me with that?
Thank you so much. I was looking for something to just count the Thursdays in the begging of the month. I used your formula, took out the IF function and took off both your plus 1's at the end. I also added the today function so I always get current date. =WEEKNUM(TODAY(),14)-WEEKNUM(EOMONTH(TODAY(),-1),14)
Hi Leila, I need a help in Week number of the month that you explained in Second option in this video. I want to have just 4 weeks in the month to be represented and last few days of a month should be counted as the Week 1 of the next month rather than week 5 of the current month. Would you be able to help me with this formulae in Excel
Hi Leyla, The approach I want to have is a bit different and I have not been able to figure it out. I need for the week that contains the 1st of the month to become week one. The fiscal week is Sun-Sat So even if the 1st of the month is Sat, that would make the dates before to be part of week 1. If you can upload a video o provide a formula correction I would be delighted.
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
Hi Leila, I’ve a problem.. My week starts on Friday, so I’d like to have any months with first one to five days of the month included to the last week of the previous month. And to include any DOW.. any suggestion as how the formula would work?
Leila I have created calendar of year 2022, in which all months are separate, but I want to highlight every week by all other background colors but in that first blank days of every month suppose 1 date is Tuesday then from Sunday to Monday are not getting highlighted by conditional format. So I want solution for it
Hi Leila, Hope you are doing good. I have a question on Weeknum function. What is difference between optional argument 2 and 11 or 1 and 17? Requesting you to answer my query. Thanks in advance.
Hi Leila, I need an help from you regarding a formula on excel. I have a survey file and I would like to see the weekly trend of survey s along with the month (i.e Week 1, Week 2). Request you to help with some suggestions on creating this report.
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!!
for added difficulty: how might i do this and represent quarters. it would seem that months over two years becomes visually cumbersome. Thank you very much for your excellent responsiveness and superior youtube lessons!
unfortunately, I'm trying to show 6 quarters all referencing the previous cell, starting with 'Period 1 Quarter(today())'...I can get to 'Period 3 Q4' pretty easily but i'm having trouble forcing the dynamic update to start back over at Q1 from Q4......I'm trying to combine many of your lessons into one and failing :(
Hello Leila, how to find monthly week number. Incase Month period is between to calendar month. For example: 11-July-2022 to 10-Aug-2022" means Jul 11 to Jul 17 is week 1, Jul 18 to Jul 24 is Week2, Jul 25 to Jul 31 is week 3, Aug 1 to Aug 7th is week 4 and finally Aug 8th to Aug 10th is Week4. Tried with this logics but wouldn't.
hi leila , many thanks . i have one query . i have weekly data for whole on eyear and i want to calculate how many weeks in each month . eg for Jan 2021 there were 5 weeks starting from 1st Jan ,8,15,22 and 29th Jan 2021. i want in output as numeric "5" for total no of week in jan and similarly 4 in feb and so on till dec
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().
How would you get round when the last day of the month is the last Monday of the the month. In this case, the WeekNo should return 1, but it returns 5, meaning there is no week 1 in October 2024. Happens every time there is this condition.
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
@@LeilaGharani But you don,t understand what i need exact ( i find Formula see it and understand what i was need ) =SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)&":"&TODAY())),"DDDD")="Friday"))-(TEXT(TODAY(),"DDDD")="Friday") - this formula can count the past Fridays which will passed gradually - in general Thanks for your interest
