First and Last Working Days of the Month Excel
HTML-код
- Опубликовано: 16 июл 2024
- First and Last working days of the month Excel - In this video, i demonstrate a formula approach to getting the first and last days of the month. The spice here is using array literals/contrast, taking advantage of pattern recognition.
Workbook link:
docs.google.com/spreadsheets/...
00:00 Introduction
00:57 First working day (Month)
02:12 Last working day (Month)
03:34 Both results dynamically
04:16 Using Array Literals
07:28 First working day for a 12-month period
08:17 Last working day for a 12-month period
09:13 Spilling a multicolumn array for first/last working days
11:32 closing thoughts
Great example of using literal constants. Thanks!
Thanks for your comment
I love your way of working with array literals. Thank you Victor.
You are welcome Ivan. Thanks for the feedback
Excellent explanation! Never seen an approach like that before, the use of arrays like that is mind blowing for me! Thank you.
Thanks Fabio. I am glad the video showed something new to you
Thanks Victor, how lucky I am to have a mentor like you!
Thanks for your kind comments, always
Sir, your video's will be a great repository and a library for excel learner's and enthusiasts.
Thank you for sharing your knowledge. Best wishes
Thank you so much Sunny for the feedback. Feel free to share with beginners 😁 and experts like yourself
Excellent explanation.
Thank you very much for the feedback
Victor.... your latest videos with using EOM and WD International and array contacts are becoming handy to me as i am starting to prepare for my financial audit for FY 2024. They only thing that i have to think about are the holidays here in the USA on how to offset those holidays from the working days. Take care!
Not a problem at all, just add I'm a list of Holidays and the function will do the rest
Perfect... Thanks. I will play around with that
Dear Momoh,
Your solution was much better than the one I sent in a comment on another video.
However, as you did not include holidays, there are some errors when the holiday falls at the beginning or end of the month, as is the case with the first day of the year which is a holiday, at least in Brazil, and is being presented as a business day.
But the fix for this is easy: just make a list of holidays and everything will be fine.
My list of holidays spans twenty years. 🤗
Yea, the answers would obviously be different once you include the holidays. The idea of the video is to introduce the concept of array constants. If anyone wants to extend it to include holidays, the fix is very simple
You guys are on the same page as me. I was just thinking about on how to offset the holidays here in the USA from the working days.
@jazzista1967 straightforward with the WORKDAY.INTL function, it already has rhe arguments
@@jazzista1967 My list of holidays was made following the script below:
Enter each holiday day in separate cells. Fixed dates are easier, as you just enter the day of the holiday. As for movable holidays, such as Easter, for example, a calculation must be made to obtain the date.
After the column with dates is complete, from a cell outside the column type the equal sign "=" and select all the dates in the column.
Click on the formula bar, which should show the selected range, and press the "F9" key.
The range will be modified and will show an array similar to this: ={44927;44946;44978}
Copy this array, press Ctrl+F3 and in the name manager click on the "new" button, give it a name "Holidays", or whatever name you want.
In the "Refers to" bar paste the matrix you copied and save.
Okay, when you want this array of holidays to be considered in calculations, just include it in the formula with the name you chose.
If you don't understand something, get in touch. 🤗