If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
It's taken me some time to play around with the formula and understand the rationale for the [row()-4] part, but I've finally found a way around it. Thanks for the great tutorial!
This formula is slightly longer than the one you used, but illustrates the power of MS 365 Array Formulas more clearly: =FILTER( SEQUENCE( EOMONTH( DATE(B1,B2,1),0 ) - DATE(B1,B2,1)+1, , DATE(B1,B2,1)), WEEKDAY(SEQUENCE(EOMONTH(DATE(B1,B2,1),0) -DATE(B1,B2,1)+1,,DATE(B1,B2,1)),2)>5) Although longer, I think it's clearer in what it's doing - Filtering an array of dates for a month by returning only Saturday and Sunday using the WEEKDAY function with option 2 to return Saturday as 6 and Sunday as 7. I only point this out because the only difference between the MS 365 formula and the Earlier Version formula is that the new formula spills rather than having to lock the values of the Month and Year and copying the formula down. Another tip (from Chandoo), use Conditional Formatting - Format only cells that contain - No Blanks, to add a border to cells with values rather than formatting the maximum range.
Super cool formula!! Refine it a little bit. =LET(m,B2,y,B1,w,{6,7},f,EDATE(1&"-"&m&"-"&y,0),e,EOMONTH(f,0),d,SEQUENCE(e-f+1,,f),FILTER(d,ISNUMBER(XMATCH(WEEKDAY(d,2),w)))) - this can handle month in cell B2 in any format 3 or Mar or March - year in B1 can be 2022 or only 22 - weekday array, " w" variable, can be any array {1,3,5} or {5,7} , so no need of >5 filter
Hi, thanks for the info. It helped me get work done. But I was not able to do the same in other PC with MS Excel 2007. How to use it in Excel 2007? Function "workday.intl" is not working there.
Hi Sumit. Great trick! I've got a version of my own, but YT keeps deleting the comment when I post it :(( Thanks always for your great and clever solutions. Thumbs up!!
Question, I’m building a spreadsheet to track pay for a client. They pay their employees on or about the 15th, and the last day of the month. If either date falls on a weekend, the the pay date is the Friday prior. Currently, I’m doing =F2-choose(weekday(F2,1),2,3,4,5,6,0,1)+E3. F1 is the starting date of the pay period, F2 is either the 15th or the last day of the month, E3 is an adjusting days that I manually enter to adjust to the previous friday. Any thoughts on how to do this without the manual adjustment?
Punit Sarda Hi. I have a problem. I have a cell with a number. Eg 123. Now I have to add say 44 to this cell. So I have to do +123+44 to get the result. But I have to this for 100 cells with different numbers and different numbers to be added. So is there any easier method to bring + or = before each number in a cell and also probably bring up a + sign after the number. Thanks
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Hi, Sumit. I enjoy your expertise in Exel. I will use some of your formulas in my work. Thanks for sharing! Stay Healthy!
Brilliant resolution. Excellent tutorial, as always. Thanks a lot.
I have searched in RUclips in this regard few days back, but I didn't get right video. Tq for sharing this video.
Awesome........Good trick......thnax for adding something new.......
Thank you for the formula for non O365 users.
It's taken me some time to play around with the formula and understand the rationale for the [row()-4] part, but I've finally found a way around it. Thanks for the great tutorial!
Glad the formula worked for you :)
magnificent upload TrumpExcel. I crushed the thumbs up on your video. Keep on up the first-rate work.
Saved my day! Thanks!
Really useful and well explained, never knew about the code string for working non working.
I learned so many new things from such a simple secenrio - Wonferful!
Thank you, thank you!!! This was what I was looking for.
Excellent
Brilliant tweak in formula 👏
Amazing Video 📹 Trump. Thank you for your hard work 👍
Wow great !!!!!!! ❤❤❤😍😍❤❤❤🤗🤗🤗🤗🙏🙏🙏🙏🙏🙏thank you so much😊
I've been looking for this for a long time, thanks!!
Glad you found the video helpful :)
Wow really great work thanks
always Great Sumit
Thank you so much..
Amazing👍👍👍👍👍⭐️
Hi Sumit, Kindly make videos on excel combined formulas as well.
Absolutely incredible. This is exactly what I needed :D
Great! thanks
clever, thanks!
Thank you very much bro
❤️
I want to know about how to make sheet 13 in excel
Thanks a los.
This formula is slightly longer than the one you used, but illustrates the power of MS 365 Array Formulas more clearly:
=FILTER( SEQUENCE( EOMONTH( DATE(B1,B2,1),0 ) - DATE(B1,B2,1)+1, , DATE(B1,B2,1)), WEEKDAY(SEQUENCE(EOMONTH(DATE(B1,B2,1),0) -DATE(B1,B2,1)+1,,DATE(B1,B2,1)),2)>5)
Although longer, I think it's clearer in what it's doing - Filtering an array of dates for a month by returning only Saturday and Sunday using the WEEKDAY function with option 2 to return Saturday as 6 and Sunday as 7. I only point this out because the only difference between the MS 365 formula and the Earlier Version formula is that the new formula spills rather than having to lock the values of the Month and Year and copying the formula down. Another tip (from Chandoo), use Conditional Formatting - Format only cells that contain - No Blanks, to add a border to cells with values rather than formatting the maximum range.
Super cool formula!! Refine it a little bit.
=LET(m,B2,y,B1,w,{6,7},f,EDATE(1&"-"&m&"-"&y,0),e,EOMONTH(f,0),d,SEQUENCE(e-f+1,,f),FILTER(d,ISNUMBER(XMATCH(WEEKDAY(d,2),w))))
- this can handle month in cell B2 in any format 3 or Mar or March
- year in B1 can be 2022 or only 22
- weekday array, " w" variable, can be any array {1,3,5} or {5,7} , so no need of >5 filter
Hi, thanks for the info. It helped me get work done. But I was not able to do the same in other PC with MS Excel 2007. How to use it in Excel 2007? Function "workday.intl" is not working there.
Hi Sumit. Great trick! I've got a version of my own, but YT keeps deleting the comment when I post it :((
Thanks always for your great and clever solutions. Thumbs up!!
Hi sumit ihave a query related sum function in gradesheet, how can you help me for the same.tanx
Dear Mr. Sumit, thanks for your great videos, but i cant fine m-o 365. Can you tell us where we can download m-office 365 free. Thanks in advance.
Question, I’m building a spreadsheet to track pay for a client. They pay their employees on or about the 15th, and the last day of the month. If either date falls on a weekend, the the pay date is the Friday prior. Currently, I’m doing =F2-choose(weekday(F2,1),2,3,4,5,6,0,1)+E3. F1 is the starting date of the pay period, F2 is either the 15th or the last day of the month, E3 is an adjusting days that I manually enter to adjust to the previous friday. Any thoughts on how to do this without the manual adjustment?
Punit Sarda
Hi. I have a problem. I have a cell with a number. Eg 123. Now I have to add say 44 to this cell. So I have to do +123+44 to get the result. But I have to this for 100 cells with different numbers and different numbers to be added. So is there any easier method to bring + or = before each number in a cell and also probably bring up a + sign after the number. Thanks
HI, NICE TO MEET YOU.
I have data for 2 months and from those 2 months I just want Friday, is that possible?
Please i ask about current account statement by excel 2016 pleas
Hi- How we can contact you for some specific helps
This is my take on the same problem
filter(filter(DATE(B1,B2,ROW(A:A)),ROW(A:A)
hello sir Sumit
I GOT NAME=? ERROR AFTER WRITING THE FORMULA
Add vore gacha video r theke
when i am doing =WORKDAY.INTL(DATE($B$1,$B$2,0),ROW()-4,"1111100") its giving me 44927 idk what it is giving as an output. Can some1 plz tell me