Great video Leila. that was a nice little twist when you talked about the formatting and showed us how to manipulate the actual start date in the formula.
Mesmerizing explanation with working days formula. Happy to view your videos. Doing a very noble job for the excel lovers/addicts/users. Thanks cannot be measured in terms of words.
Hi Leila, many thanks for the video, it got me a long way along the tortuous route I have to take get my forecasting correct. One thing: in construction, the start date is usually counted as a work day which Excel miscalculates unless the first calculated date is based upon Start Date-1.
Thank you.. Simple lesson with very direct example. Any one would really understand this function easily by this video. This is very helpful for projects scheduling and detailed planning. Great effort .. I have two questions for you if you can help! 1. How to get the start of the month or end of the month for each data in a list? 2. How can i use the excel as an alarm (using what ever VBA or functions ) for a certain date to remind for something (like cheque date, meeting etc) when i open my pc - if possible- or open any excel sheet ?
Hi Abdelrahman, Glad you like the video. For 1) the EOMONTH(date,month) function is the one that returns the last date of the month - if you pick 0 for the month argument, it gives you the last day of the existing month. You can format that to show you the day of the week as well. For 2) Conditional formatting might be good here. You can use the today() function to give you today's date - compare this to your deadlines and conditionally format cells or icons to show you you red if you are past the deadlines - I will add this to my list to make a video on it :)
Hi Leila Gharani Thank you for your response For 1) your solution will be good for the issue. For 2) your solution means that I should open designed sheet and check for the highlighted (conditionally formatted cells) to see the alarmed cell! But i need a bob up massage from excel to my desktop (if possible) even if I didn't open excel to alarm me about any timed issue (same like alarm in Mobile phone!! ) - as I said if possible.
Thanks! Is there a way to round the end date to the nearest, say, Thursday? But if it already lands on a Thursday, it should stay on that Thursday rather than rounding up or down.
Hi Leila If you had to calculate days which include Saturdays and also if a holiday fell on a Saturday. How would you go about creating a formula to calculate days in this scenario? Thank you AJ
If I am using an averageifs function to calculate monthly totals. Is it possible to nest the network days function in the criteria to exclude weekends to get a more accurate average calculation.
what about if we have shifts and the off days are not the same all the time and we rarely happen to get Saturday and Sunday as day offs. how we gonna work it in this case. thank you
I have set of data wherein I have start end and end date but there manay cells therein has no end date given (there are blank as task has not completed) in this case which formula I should use and also I have to exlcude weekend.. Pls suggest.
What is the difference between workday and networkdays formula?, I am getting difference count between these two formulas for same days e.g. 1st June 2020 to 30th June 2020.
Dear Leila ! I did put the start date and duration in the WORKDAY function, but instead of getting an end date the answer was things like 10567 or 44635! Plz help me ! Thanks!
we do substantially appreciate your customary support, uhmm could you plz let us know how to calculate end date with time and date if for example i want to add 3 working-days to 7/1/2020 2:35 PM getting exact end date and time, much love :)
I used the TODAY function to input today's date. Let's say for example I use =Today() in a cell and it inputs 6/6/2020. Tomorrow, in that cell the TODAY function will input 6/7/2020. I don't want that. Is there a function where I can input today's date and it stays that same date every day afterwards? Thanks in advance. You rock!
Great Video, just what I was looking for. How would I include another 'weekend day'? My working days are only 4 days out of the week. So should I just make a list of 52 'monday's as part of the holiday list? Or would there be another way to make the 'weekend' 3 days?
Hiya, I have a question. How do you add three months to a start given date. I only want working days within that 3 months and I don't know which formula to use. Some months have bank holidays so of course I want to exclude it. Please help?
If today is Friday and date is 1-july-2022 and in cell i want Monday's date so what will be formula. Means next cell will always omit Saturday and Sunday date and return Monday date...pls help
Hi Leila.I wanted to get a black theme in office 16 by adjusting the UI theme to 4 and I also followed other prescribed steps, but nothing was changing.What should I do?
I enjoy your videos and watch them if YT puts them on the home page which is why I'm writing almost 4 years later. In the first example suppose the Estimated Data was due on the first of the month, so you put 0 in the Due On Working Day column. By subtracting 1 from the start date, the due date becomes the day before. If you remove the subtraction, May and June work fine, however April 1 is a Saturday, and October 1 is a Sunday, so I would expect WORKDAY to take this account and return 4/3/2017 and 10/2/2017, however they both return the first of the month. Changing the 0 working days to 1, they correctly return 4/3 and 10/2. The reason I saw this was because I was thinking that the start date itself should be wrapped in the WORKDAY function with 0 days as the days parameter to compensate for months that start on the weekend. This also happens with the .INTL version. Your thoughts?
In our office it's odd and even saturdays. All odd saturdays are working and even saturdays are holidays of course all Sundays are off. So how should we do that in planning?
So I have some problems with my homework: you need to find the dates of delivering from transport company to market. you have shipping date, duration of delivery (days), holiday day and weekend days (Sat, Sun). But the delivery works without day off while market doesn't work only sat,sun and holidays. How can I calculate it without using IF function?
What if I have the beginning and end date for the total project, but percentage of time for different categories are being allocated. For ex: a Project starts on 01/01/2021 and ends 05/31/2021. There are 5 categories: 10% Planning, 10% Project Design, 60% Building, 10% Test, and 10% Deploy. How can I calculate the end date for each category flowing in that order with a percentage and exclude weekends/holidays?
Hi Leila, i have excel O365, to calculate end date excluding weekend, i used the same formula but it is giving me incorrect result, Start Date: 02-Mar-2020 Days : 10 Result : 16-Mar-2020 the formula i applied is :=WORKDAY(A2,C2) A2 = Start Date C2 = Days
Hi Just a quick question, I have a long list (15 years) in one column and values corresponding to each day. The problem is the dates are without weekdays and apparently I need them, so is there any way to add weekdays making the list longer but also keep the values in the second column still attached to the dates they were? So the values for the weekdays would be empty for now? I've been looking for solution for a while now and cannot find anything...
How can i calculate specific date after specified period with condition on specific day of week. As example lets just say. From today i want someone to come for appointmnet after 3 months but it should be tuesday
This function continuously returns #VALUE, in spite of the fact that my starting date function is formatted as a date value and my # of Work Days is formatted as a number?
Hi Leila , how to use RANDBETWEEN FUNCTION FOR FUNCTION : =RANDBETWEEN(3,15) DOES this mean random or volatile the number from 3 to 15 ? how is this RANDBETWEEN works for and normally what cases will use this formula? Thanks you very much
For a list of employees , want to calculate no: of days they worked in a year starting with date of increment due date to current month. Increment beginning date is different for all employees and we are provided with the attendances of all those employees of a one or two years or more. From that range of attendance , want to calculate only the eligible days from the date of increment date to the current month
Thank you Ubaid - do you mean inside Excel formulas? The only place ! comes to mind is for sheet referencing and "," is for the Excel separator - depending on the arguments a function has..... or do you have some specific examples?
Ya mam i mean inside excel formulas...like mam if u get me i just wanna ask ...Conceptually what is excel seperator why n when it is use.... plus "," and "",""....same thing???
How can I find the end date if I assign different hours to the week days, so not every day would have the same work hours, and how can I fully customise the weekdays if I need more than 2? I have been struggle with this for a while now
Hi Manu - best is to post your specific question either here: techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
I am having a hard time figuring out a formula in this case. 29th October is a Friday and 01st November is a Monday. Our Machine stops operating at 10:30pm on Friday and starts on 31st October Sunday Night 9:30pm. I need to figure out a formula that gives me the total hours excluding that gap from 10:30pm on Friday to 9:30 pm on Sunday Night. Can anyone help?
hi...madam.... madam I have one query.. how can make some value to zero in particular cell....if I Enter some things in other cell....by conditional formatting...
I am trying something similar I have a situation where I need to find out say 10 days in the future as well as counting the start date. Also, if the day lands on a saturday it needs to minus 1 day and give me the date of the friday (day before) and same goes if the day lands on sunday it will add 1 to give me the date for the monday (day after). Could this be possible if so can you help me?
You can use the weekday function to find out if you come across a saturday or sunday. Here are some videos that can help: ruclips.net/video/G6ImRBn3cQk/видео.html and ruclips.net/video/WlC4IvjOCNo/видео.html
Hello .. I'm following all your videos from last week. Your videos are very useful.. I have once quick question for you , Please help me out because I'm stuck here. Question : For ex : I have a one date as a reference and from this reference date i need to get 3 different dates i.e ( 7 days before the ref date, 2 days before the ref date & 1 day before the ref date) but the twist is that the new date should fall on Weekday and not on Weekend( i.e Sat & sun) & Holidays. Please help me out , i'm trying to figure out this from many days.
Does anyone have an idea how to create and use list of working Saturdays and use that list (with opposite criteria) as holiday list? VBA is acceptable as solution too :)
if anyone is interesting in, I find solution: =LAMBDA(start_day,number_of_working_days,list_holiday,list_working_weekends, WORKDAY(start_day, number_of_working_days-COUNTIFS(list_working_weekends,""&start_day), list_holiday))
Great tutorial; however I don't want it to calculate so that it takes out all the weekends. I just want the end date not to be a weekend. If anyone can help with that it would be appreciated.
how to find out the work end date before a certain date?, for example, I want to get 30 April 2017 as the work end date, but 30 April 2017 is Sunday, then the work end date is 28 April 2017?
I have created this function to get first date of week as Thursday in ms access form and reports, but this function return first day of week in las month, but I need it will return only current month dates, if current week first day is in last month then retrun first day of week as 1st date of week in current month. ‘current output ‘when 1-10-2018 is selected from date picker it returns 27-09-2018, which is first date of week in last month but I need it will return 1st of month which is selected in textbox. Function GetFirstofWeek(dtDate As Date) 'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1) ' Excel formula for this function which is perfect and may be converted to function as required ‘=MAX(B2-WEEKDAY(B2,14)+1, EOMONTH(B2, -1)+1) GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate, vbThursday) - 1)) End Function 'For last day of week Function GetLastofWeek(dtDate) as date '=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0)) GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function In this function last date of week must be in current month, if month end on first date of then week will be closed and last date of week will be 1st date of week or days remaining in current week.
Grab the file I used in the video from here 👉 pages.xelplus.com/workday-function-file
Great video Leila. that was a nice little twist when you talked about the formatting and showed us how to manipulate the actual start date in the formula.
Thank you Michael :)
Thanks for the WorkDay fun : )
You're welcome Mike. Hope you're having a great time off.
Mesmerizing explanation with working days formula. Happy to view your videos. Doing a very noble job for the excel lovers/addicts/users. Thanks cannot be measured in terms of words.
Thanks Gopala. Glad you like it.
Thanks to your informative video, it made my work easy for creating day to day schedule with drafting works
Teaching methods are very useful
Glad you like it.
Hi Leila
Could you explain how to auto fill last working of the month
Hi Leila, many thanks for the video, it got me a long way along the tortuous route I have to take get my forecasting correct. One thing: in construction, the start date is usually counted as a work day which Excel miscalculates unless the first calculated date is based upon Start Date-1.
Hi Leila........Your teaching is commendable. Thanks for your teachings. What if someone has holiday on every Sunday but alternate Saturday.
Hi Leila, Could you pls make a video on how to calculate estimated end time for each task, if we have start time and duration in hours ?
Very helpful... thanks for sharing!
Great tip! How do I exclude weekends and holidays in 'remaining days' for deadline, can this be done?
Always good to watch your videos. They are so informatively explained. :)
Thanks Sachin.
Super easy to follow.
Glad you think so!
Hi thank you for this...and also if you have easy way to track leave of the staffs...thank you
Good day, may I ask, if it’s possible to count by month? Like if now is Jan, A due date is every two month. Any formula to work out ? Thanks
Thanks for the work day
thank you very much this video help me a lot
Hi Leila, how to eliminate return 1 if dates are blank
Hi Leila, I want to do this with Hours instead of days, how would it be possible ?
What function did you use to insert the small full month calendars on the right? Or are they inserted images?
Thank you..
Simple lesson with very direct example. Any one would really understand this function easily by this video.
This is very helpful for projects scheduling and detailed planning.
Great effort ..
I have two questions for you if you can help!
1. How to get the start of the month or end of the month for each data in a list?
2. How can i use the excel as an alarm (using what ever VBA or functions ) for a certain date to remind for something (like cheque date, meeting etc) when i open my pc - if possible- or open any excel sheet ?
Hi Abdelrahman, Glad you like the video. For 1) the EOMONTH(date,month) function is the one that returns the last date of the month - if you pick 0 for the month argument, it gives you the last day of the existing month. You can format that to show you the day of the week as well.
For 2) Conditional formatting might be good here. You can use the today() function to give you today's date - compare this to your deadlines and conditionally format cells or icons to show you you red if you are past the deadlines - I will add this to my list to make a video on it :)
Hi Leila Gharani
Thank you for your response
For 1) your solution will be good for the issue.
For 2) your solution means that I should open designed sheet and check for the highlighted (conditionally formatted cells) to see the alarmed cell!
But i need a bob up massage from excel to my desktop (if possible) even if I didn't open excel to alarm me about any timed issue (same like alarm in Mobile phone!! ) - as I said if possible.
For 2) I personally can't think of any excel VBA that could do that - since that would be kept with the workbook and can run if you open Excel.....
This is possible in power query?. I needed in power query ,, make video on it.
You are just wonderful
nicely explained
Thanks! Is there a way to round the end date to the nearest, say, Thursday? But if it already lands on a Thursday, it should stay on that Thursday rather than rounding up or down.
Is it possible in excel to do the reverse for the Start & End Dates, which you generously explained.
Great explanation. Thanks you so much Leila.
You're welcome Sanjay.
Hi Leila
If you had to calculate days which include Saturdays and also if a holiday fell on a Saturday. How would you go about creating a formula to calculate days in this scenario?
Thank you
AJ
If I am using an averageifs function to calculate monthly totals. Is it possible to nest the network days function in the criteria to exclude weekends to get a more accurate average calculation.
Whenever I miss the perfect of something ,I go to leila to catch the perfect , thanks to the lady of perfect .
"This is a weekend and a holiday. It's very unfortunate" :)
Another great vid from Leila .... btw. does anybdy know how to make circular calendars (or plate calendars) in excel or connected ppt?
Thank for this video
Hi Leila when I use this it calculate one more day , how can i solve this
what about if we have shifts and the off days are not the same all the time and we rarely happen to get Saturday and Sunday as day offs. how we gonna work it in this case. thank you
Thank you ,and I would like to ask you about the holiday and weekends column , did you type in the dates by yourself or there are any easier method ?
Hi Leila, Thanks for the video.
Is there a way to calculate the start date out from the End date and the duration?
Thanks
I have set of data wherein I have start end and end date but there manay cells therein has no end date given (there are blank as task has not completed) in this case which formula I should use and also I have to exlcude weekend.. Pls suggest.
How to used workdays function if my Saturday is alternative holiday and all Sunday holiday.
Please help
What is the difference between workday and networkdays formula?, I am getting difference count between these two formulas for same days e.g. 1st June 2020 to 30th June 2020.
want to calculate, working hours including weekends..can u help
I need to create a series of dates that display monday tuesdays and thursdays only how could i do that useing the workday function ?
Dear Leila ! I did put the start date and duration in the WORKDAY function, but instead of getting an end date the answer was things like 10567 or 44635! Plz help me ! Thanks!
Thanks, this is great! Can you show how to include certain Saturdays that will count as workdays?
we do substantially appreciate your customary support, uhmm could you plz let us know how to calculate end date with time and date if for example i want to add 3 working-days to 7/1/2020 2:35 PM getting exact end date and time, much love :)
What would the formula be if you wanted to have the start date of project 2 the day after the end date of project 1 while excluding Sunday’s?
Excellent, as usual :-)
Thanks Alex :)
thanks and this is the best thing i have learned
I'm glad to hear that Yonas!
I used the TODAY function to input today's date. Let's say for example I use =Today() in a cell and it inputs 6/6/2020. Tomorrow, in that cell the TODAY function will input 6/7/2020. I don't want that. Is there a function where I can input today's date and it stays that same date every day afterwards? Thanks in advance. You rock!
If we have any task which will complete in half days can you tell how it will work
Great Video, just what I was looking for. How would I include another 'weekend day'? My working days are only 4 days out of the week. So should I just make a list of 52 'monday's as part of the holiday list? Or would there be another way to make the 'weekend' 3 days?
Thank you that's really helpful!
what is someone works for half day on Saturday including Monday to Friday how do i do include that half day?
Hiya, I have a question. How do you add three months to a start given date. I only want working days within that 3 months and I don't know which formula to use. Some months have bank holidays so of course I want to exclude it. Please help?
If today is Friday and date is 1-july-2022 and in cell i want Monday's date so what will be formula. Means next cell will always omit Saturday and Sunday date and return Monday date...pls help
Hi thanks for the video, interesting one. How did you insert the calendar in the sheet?
You're welcome Mark - Snipping Tool did the job.
@@LeilaGharani Hi coach, is it possible to bring these type of calendar excel way (rather snipping)
Hello,can someone help me ,i would like to use this ,but i only want exclude holidays,
hi LEILA, how do i do this if i need to include weekends and holidays.
Hi Leila.I wanted to get a black theme in office 16 by adjusting the UI theme to 4 and I also followed other prescribed steps, but nothing was changing.What should I do?
Hi - if you go to Excel options - under general, you have a drop-down for theme. Make your selection there...
What if starting date and end date is same for a task?
I enjoy your videos and watch them if YT puts them on the home page which is why I'm writing almost 4 years later. In the first example suppose the Estimated Data was due on the first of the month, so you put 0 in the Due On Working Day column. By subtracting 1 from the start date, the due date becomes the day before. If you remove the subtraction, May and June work fine, however April 1 is a Saturday, and October 1 is a Sunday, so I would expect WORKDAY to take this account and return 4/3/2017 and 10/2/2017, however they both return the first of the month. Changing the 0 working days to 1, they correctly return 4/3 and 10/2. The reason I saw this was because I was thinking that the start date itself should be wrapped in the WORKDAY function with 0 days as the days parameter to compensate for months that start on the weekend. This also happens with the .INTL version. Your thoughts?
What if you have an end date and number of work day and need to know when the date in the past it should start?
In our office it's odd and even saturdays. All odd saturdays are working and even saturdays are holidays of course all Sundays are off. So how should we do that in planning?
So I have some problems with my homework: you need to find the dates of delivering from transport company to market. you have shipping date, duration of delivery (days), holiday day and weekend days (Sat, Sun). But the delivery works without day off while market doesn't work only sat,sun and holidays. How can I calculate it without using IF function?
Veldig bra forklart. Nice.
What if I have the beginning and end date for the total project, but percentage of time for different categories are being allocated. For ex: a Project starts on 01/01/2021 and ends 05/31/2021. There are 5 categories: 10% Planning, 10% Project Design, 60% Building, 10% Test, and 10% Deploy. How can I calculate the end date for each category flowing in that order with a percentage and exclude weekends/holidays?
Is there any way to find out 7th working day, if employees does not have work off on consecutive days?
Hi Leila, i have excel O365, to calculate end date excluding weekend, i used the same formula but it is giving me incorrect result,
Start Date: 02-Mar-2020
Days : 10
Result : 16-Mar-2020
the formula i applied is :=WORKDAY(A2,C2)
A2 = Start Date
C2 = Days
How can you exclude the start date
Hi
Just a quick question, I have a long list (15 years) in one column and values corresponding to each day. The problem is the dates are without weekdays and apparently I need them, so is there any way to add weekdays making the list longer but also keep the values in the second column still attached to the dates they were? So the values for the weekdays would be empty for now? I've been looking for solution for a while now and cannot find anything...
How can i calculate specific date after specified period with condition on specific day of week. As example lets just say. From today i want someone to come for appointmnet after 3 months but it should be tuesday
Thanks a lot. However, what about the dashboard course? I sent a mail but have not received any feedback
Hi Robert, you're welcome. I haven't received any specific email on the dashboard course - have you sent it o the email listed on my site?
thanks
This function continuously returns #VALUE, in spite of the fact that my starting date function is formatted as a date value and my # of Work Days is formatted as a number?
same as mine. Have u find the solution
Hi Leila , how to use RANDBETWEEN FUNCTION FOR FUNCTION : =RANDBETWEEN(3,15)
DOES this mean random or volatile the number from 3 to 15 ?
how is this RANDBETWEEN works for and normally what cases will use this formula?
Thanks you very much
The randbetween is a volatile function so it calculates all the time. Randbetween gives you random numbers between the numbers you specify.
For a list of employees , want to calculate no: of days they worked in a year starting with date of increment due date to current month. Increment beginning date is different for all employees and we are provided with the attendances of all those employees of a one or two years or more. From that range of attendance , want to calculate only the eligible days from the date of increment date to the current month
How can i contact u ma'm
mam very nice video...mam i wanna ask a thing how can i master the meaning of '','' or ! or such sort of symbols use in excel....
Thank you Ubaid - do you mean inside Excel formulas? The only place ! comes to mind is for sheet referencing and "," is for the Excel separator - depending on the arguments a function has..... or do you have some specific examples?
Ya mam i mean inside excel formulas...like mam if u get me i just wanna ask ...Conceptually what is excel seperator why n when it is use.... plus "," and "",""....same thing???
Hi Ubaid - Not sure about this... could you give an example of a full formula?
thanks leila
You're welcome Mohammad.
How can I find the end date if I assign different hours to the week days, so not every day would have the same work hours, and how can I fully customise the weekdays if I need more than 2? I have been struggle with this for a while now
Here I have my spreadsheet where I tried to find a solution: 1drv.ms/x/s!AtYtwUtCJl08g_9dmnVOZ1U0oA-nzw
Hi Manu - best is to post your specific question either here: techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral or here: www.excelforum.com/ - you can post a link to your question here too and if I get a chance I can take a look, otherwise you will get answers from others...
I am having a hard time figuring out a formula in this case. 29th October is a Friday and 01st November is a Monday. Our Machine stops operating at 10:30pm on Friday and starts on 31st October Sunday Night 9:30pm. I need to figure out a formula that gives me the total hours excluding that gap from 10:30pm on Friday to 9:30 pm on Sunday Night. Can anyone help?
hi...madam....
madam I have one query..
how can make some value to zero in particular cell....if I Enter some things in other cell....by conditional formatting...
Hi Alpesh - the IF function would work well here....
awesome !!!
:) You're welcome.
I am trying something similar I have a situation where I need to find out say 10 days in the future as well as counting the start date. Also, if the day lands on a saturday it needs to minus 1 day and give me the date of the friday (day before) and same goes if the day lands on sunday it will add 1 to give me the date for the monday (day after). Could this be possible if so can you help me?
You can use the weekday function to find out if you come across a saturday or sunday. Here are some videos that can help: ruclips.net/video/G6ImRBn3cQk/видео.html and ruclips.net/video/WlC4IvjOCNo/видео.html
just subtract one in your formula after the reference cell
Hello .. I'm following all your videos from last week. Your videos are very useful..
I have once quick question for you , Please help me out because I'm stuck here.
Question : For ex : I have a one date as a reference and from this reference date i need to get 3 different dates i.e ( 7 days before the ref date, 2 days before the ref date & 1 day before the ref date) but the twist is that the new date should fall on Weekday and not on Weekend( i.e Sat & sun) & Holidays.
Please help me out , i'm trying to figure out this from many days.
Does anyone have an idea how to create and use list of working Saturdays and use that list (with opposite criteria) as holiday list?
VBA is acceptable as solution too :)
if anyone is interesting in, I find solution:
=LAMBDA(start_day,number_of_working_days,list_holiday,list_working_weekends,
WORKDAY(start_day,
number_of_working_days-COUNTIFS(list_working_weekends,""&start_day),
list_holiday))
Great tutorial; however I don't want it to calculate so that it takes out all the weekends. I just want the end date not to be a weekend. If anyone can help with that it would be appreciated.
Awesome
how to find out the work end date before a certain date?, for example, I want to get 30 April 2017 as the work end date, but 30 April 2017 is Sunday, then the work end date is 28 April 2017?
I have found it=WORKDAY.INTL(DATE(2017;5;1); -1;1;$G$3:$G$17)
#value error is coming while applying this formula, please help
Try downloading the workbook from the link provided in the description of the video and cross-check with your version.
@@LeilaGharani thanks !
Guys, about Excel, do you know any formula for bringing the number of the working day of the month? Ex 3/15/2021 = 11, 3/16/2021 = 12
When I followed the procedure, the end date became " #value!". How to fix this?
how to fetch the holidays for free with webservice?
Nice
How to get any Sunday date. ex.get date of 4th Sunday of September 2019
why does it keep popping up #VALUE this is so frustrating
complicated ... to claculate repeat rows
There are so many comments and queries @leila but you reply hardly to any.
I have created this function to get first date of week as Thursday in ms access form and reports, but this function return first day of week in las month, but I need it will return only current month dates, if current week first day is in last month then retrun first day of week as 1st date of week in current month.
‘current output
‘when 1-10-2018 is selected from date picker it returns 27-09-2018, which is first date of week in last month but I need it will return 1st of month which is selected in textbox.
Function GetFirstofWeek(dtDate As Date)
'GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate)) + 1)
' Excel formula for this function which is perfect and may be converted to function as required ‘=MAX(B2-WEEKDAY(B2,14)+1, EOMONTH(B2, -1)+1)
GetFirstofWeek = DateAdd("d", dtDate, -(Weekday(dtDate, vbThursday) - 1))
End Function
'For last day of week
Function GetLastofWeek(dtDate) as date
'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function
In this function last date of week must be in current month, if month end on first date of then week will be closed and last date of week will be 1st date of week or days remaining in current week.