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.
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.
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.
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!
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
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?
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?
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 :)
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! 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.
You can use it together with the IF function (ua-cam.com/video/KkTaQ5OjAGc/v-deo.html). Depending on the number you get returned you can add 1 or 2 days to the date to get to the next Monday. I have some more date videos that could help with that: ua-cam.com/video/WlC4IvjOCNo/v-deo.html
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.
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.
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.
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?
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
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
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
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?
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?
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?
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
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.
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
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 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...
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???
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...
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?
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?
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...
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.
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
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: ua-cam.com/video/G6ImRBn3cQk/v-deo.html and ua-cam.com/video/WlC4IvjOCNo/v-deo.html
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))
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.
I have question to ask, I want to display first and day of week, week starts from Thursday to Wednesday, if the start date of month is on tuesday then first date of week will be tuesday of the current month, same case on last date of week, if week ends on monday then last date of week will be monday of only in current month, without going to next month last week day. Out expected For last month 1-09-2018 to 05-09-2018 27-09-2018 to 30-09-2018 For current month 1-10-2018 to 3-10-2018 4-10-2018 to 10-10-2018 11-10-2018 to 17-10-2018 18-10-2018 to 24-10-2018 25-10-2018 to 31-10-2018
Grab the file I used in the video from here 👉 pages.xelplus.com/workday-function-file
Hi Leila........Your teaching is commendable. Thanks for your teachings. What if someone has holiday on every Sunday but alternate Saturday.
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 :)
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 ?
Thanks to your informative video, it made my work easy for creating day to day schedule with drafting works
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.
Great tip! How do I exclude weekends and holidays in 'remaining days' for deadline, can this be done?
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.....
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.
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
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!
Teaching methods are very useful
Glad you like it.
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
Hi thank you for this...and also if you have easy way to track leave of the staffs...thank you
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?
Very helpful... thanks for sharing!
Thanks, this is great! Can you show how to include certain Saturdays that will count as workdays?
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?
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 :)
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 ?
Another great vid from Leila .... btw. does anybdy know how to make circular calendars (or plate calendars) in excel or connected ppt?
Hi Leila, Thanks for the video.
Is there a way to calculate the start date out from the End date and the duration?
Thanks
What function did you use to insert the small full month calendars on the right? Or are they inserted images?
Hi Leila, I want to do this with Hours instead of days, how would it be possible ?
Hi Leila
Could you explain how to auto fill last working of the month
Thanks for the work day
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!
thank you very much this video help me a lot
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.
Always good to watch your videos. They are so informatively explained. :)
Thanks Sachin.
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.
Super easy to follow.
Glad you think so!
This is possible in power query?. I needed in power query ,, make video on it.
this is great!. using this same example how can I use formula to check and confirm that the start date is never a weekend/holiday
You can use it together with the IF function (ua-cam.com/video/KkTaQ5OjAGc/v-deo.html). Depending on the number you get returned you can add 1 or 2 days to the date to get to the next Monday. I have some more date videos that could help with that: ua-cam.com/video/WlC4IvjOCNo/v-deo.html
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?
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.
"This is a weekend and a holiday. It's very unfortunate" :)
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.
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.
nicely explained
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?
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 Leila when I use this it calculate one more day , how can i solve this
You are just wonderful
How to used workdays function if my Saturday is alternative holiday and all Sunday holiday.
Please help
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 need to create a series of dates that display monday tuesdays and thursdays only how could i do that useing the workday function ?
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.
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?
Thank for this video
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...
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?
want to calculate, working hours including weekends..can u 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)
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
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?
Hello,can someone help me ,i would like to use this ,but i only want exclude holidays,
Is there any way to find out 7th working day, if employees does not have work off on consecutive days?
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.
If we have any task which will complete in half days can you tell how it will work
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?
hi LEILA, how do i do this if i need to include weekends and holidays.
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
what is someone works for half day on Saturday including Monday to Friday how do i do include that half day?
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?
thanks and this is the best thing i have learned
I'm glad to hear that Yonas!
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...
Whenever I miss the perfect of something ,I go to leila to catch the perfect , thanks to the lady of perfect .
Thank you that's really helpful!
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
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
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....
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
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)
What if starting date and end date is same for a task?
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...
Veldig bra forklart. Nice.
thanks leila
You're welcome Mohammad.
Excellent, as usual :-)
Thanks Alex :)
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.
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
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: ua-cam.com/video/G6ImRBn3cQk/v-deo.html and ua-cam.com/video/WlC4IvjOCNo/v-deo.html
just subtract one in your formula after the reference cell
How can you exclude the start date
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))
When I followed the procedure, the end date became " #value!". How to fix this?
Awesome
awesome !!!
:) You're welcome.
#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 !
Nice
how to fetch the holidays for free with webservice?
How to get any Sunday date. ex.get date of 4th Sunday of September 2019
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.
There are so many comments and queries @leila but you reply hardly to any.
I have question to ask,
I want to display first and day of week, week starts from Thursday to Wednesday, if the start date of month is on tuesday then first date of week will be tuesday of the current month, same case on last date of week, if week ends on monday then last date of week will be monday of only in current month, without going to next month last week day.
Out expected
For last month
1-09-2018 to 05-09-2018
27-09-2018 to 30-09-2018
For current month
1-10-2018 to 3-10-2018
4-10-2018 to 10-10-2018
11-10-2018 to 17-10-2018
18-10-2018 to 24-10-2018
25-10-2018 to 31-10-2018
why does it keep popping up #VALUE this is so frustrating