Grab the file I used in the video from here 👉 pages.xelplus.com/workday-function-file
@michaeldiamond2726
7 жыл бұрын
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.
@LeilaGharani
7 жыл бұрын
Thank you Michael :)
@philipmcdonnell7168
5 жыл бұрын
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.
@excelisfun
7 жыл бұрын
Thanks for the WorkDay fun : )
@LeilaGharani
7 жыл бұрын
You're welcome Mike. Hope you're having a great time off.
@prayagkudalkar5435
4 жыл бұрын
Hi Leila........Your teaching is commendable. Thanks for your teachings. What if someone has holiday on every Sunday but alternate Saturday.
@carlos61028
4 жыл бұрын
Thanks to your informative video, it made my work easy for creating day to day schedule with drafting works
@divyanshusaxena4079
Жыл бұрын
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 ?
@1gopalakrishnarao
7 жыл бұрын
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.
@LeilaGharani
7 жыл бұрын
Thanks Gopala. Glad you like it.
@abdanomer
7 жыл бұрын
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 ?
@LeilaGharani
7 жыл бұрын
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 :)
@abdanomer
7 жыл бұрын
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.
@LeilaGharani
7 жыл бұрын
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.....
@vijaykapse20
5 жыл бұрын
Teaching methods are very useful
@LeilaGharani
5 жыл бұрын
Glad you like it.
@amanmiah5040
3 жыл бұрын
Thanks for the work day
@mariaaissalagumbay1145
Жыл бұрын
Hi thank you for this...and also if you have easy way to track leave of the staffs...thank you
@gpi1490
5 жыл бұрын
"This is a weekend and a holiday. It's very unfortunate" :)
@721rima
3 жыл бұрын
Very helpful... thanks for sharing!
@DrTriciaMagee
8 ай бұрын
Super easy to follow.
@LeilaGharani
8 ай бұрын
Glad you think so!
@bakirkr
4 жыл бұрын
thank you very much this video help me a lot
@ucirlos
3 жыл бұрын
Thanks, this is great! Can you show how to include certain Saturdays that will count as workdays?
@jerrydellasala7643
3 жыл бұрын
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?
@jksharma7
3 жыл бұрын
You are just wonderful
@hosseinhosseinpoor4845
3 жыл бұрын
thanks
@SanjayKumar-yx6gc
7 жыл бұрын
Great explanation. Thanks you so much Leila.
@LeilaGharani
7 жыл бұрын
You're welcome Sanjay.
@sachinrv1
6 жыл бұрын
Always good to watch your videos. They are so informatively explained. :)
@LeilaGharani
6 жыл бұрын
Thanks Sachin.
@tha2irtalib343
5 жыл бұрын
Whenever I miss the perfect of something ,I go to leila to catch the perfect , thanks to the lady of perfect .
@mahamahmoud4570
4 жыл бұрын
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 :)
@dramarecaps2731
4 жыл бұрын
Thank for this video
@sajjadhossainMusic
4 жыл бұрын
nicely explained
@piusthomas3900
2 жыл бұрын
Hi Leila Could you explain how to auto fill last working of the month
@guanyinbaby
2 жыл бұрын
Great tip! How do I exclude weekends and holidays in 'remaining days' for deadline, can this be done?
@colynwangchiiyen8111
6 жыл бұрын
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
@LeilaGharani
6 жыл бұрын
You can use it together with the IF function (kzitem.info/news/bejne/rKGKx4VriJxyepg). 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: kzitem.info/news/bejne/uKJ5mn2so4F0gaQ
@yonasmisganaw6023
6 жыл бұрын
thanks and this is the best thing i have learned
@LeilaGharani
6 жыл бұрын
I'm glad to hear that Yonas!
@only4fun38
5 жыл бұрын
Thank you that's really helpful!
@mohammadasad9890
7 жыл бұрын
thanks leila
@LeilaGharani
7 жыл бұрын
You're welcome Mohammad.
@sarabayi4449
4 жыл бұрын
Hi Leila, Thanks for the video. Is there a way to calculate the start date out from the End date and the duration? Thanks
@Ju-S-Ka
4 жыл бұрын
Another great vid from Leila .... btw. does anybdy know how to make circular calendars (or plate calendars) in excel or connected ppt?
@64ALS
4 жыл бұрын
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
@qred1970
4 жыл бұрын
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!
@ocbroadband
4 жыл бұрын
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?
@Robert19637
6 жыл бұрын
Veldig bra forklart. Nice.
@chengqian6406
5 жыл бұрын
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 ?
@leekuanyew6219
2 жыл бұрын
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
@alexrosen8762
7 жыл бұрын
Excellent, as usual :-)
@LeilaGharani
7 жыл бұрын
Thanks Alex :)
@mafiaeae
4 жыл бұрын
Is it possible in excel to do the reverse for the Start & End Dates, which you generously explained.
@ca777
4 жыл бұрын
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?
@safeeras3645
4 жыл бұрын
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
@Viruchikhli
3 жыл бұрын
This is possible in power query?. I needed in power query ,, make video on it.
@southpaww
5 жыл бұрын
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.
@piyushsinha1659
5 жыл бұрын
Awesome
@ncnc2896
7 жыл бұрын
awesome !!!
@LeilaGharani
7 жыл бұрын
:) You're welcome.
@Shankar_Sharma_Official
6 жыл бұрын
Nice
@terrytaylor5941
4 жыл бұрын
What function did you use to insert the small full month calendars on the right? Or are they inserted images?
@fahedayub
2 жыл бұрын
Hi Leila, I want to do this with Hours instead of days, how would it be possible ?
@eswarraom8077
4 жыл бұрын
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.
@Someonereallycares
11 ай бұрын
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?
@yonasmisganaw6023
6 жыл бұрын
what is someone works for half day on Saturday including Monday to Friday how do i do include that half day?
@billa19888
2 жыл бұрын
How to used workdays function if my Saturday is alternative holiday and all Sunday holiday. Please help
@bahramaghakhan1024
4 жыл бұрын
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!
@bluchap43
6 жыл бұрын
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.
@FREAK12111
Жыл бұрын
I need to create a series of dates that display monday tuesdays and thursdays only how could i do that useing the workday function ?
@ereklexuxua6837
Жыл бұрын
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?
@syedghousebasha5402
2 жыл бұрын
want to calculate, working hours including weekends..can u help
@userme2803
7 жыл бұрын
Hi thanks for the video, interesting one. How did you insert the calendar in the sheet?
@LeilaGharani
7 жыл бұрын
You're welcome Mark - Snipping Tool did the job.
@nicenicenicenicee
5 жыл бұрын
@@LeilaGharani Hi coach, is it possible to bring these type of calendar excel way (rather snipping)
@kathyziegler8499
5 жыл бұрын
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.
@rehanansari5674
4 жыл бұрын
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?
@prakashrawat2015
4 жыл бұрын
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
@younessblackmood6485
5 жыл бұрын
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
@arunkottayan1160
5 жыл бұрын
If we have any task which will complete in half days can you tell how it will work
@krunalmotka84
4 жыл бұрын
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
@NilabKhwajazada
Жыл бұрын
Hi Leila when I use this it calculate one more day , how can i solve this
@smartyi2782
6 жыл бұрын
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
@LeilaGharani
6 жыл бұрын
The randbetween is a volatile function so it calculates all the time. Randbetween gives you random numbers between the numbers you specify.
@YagoCamargo
3 жыл бұрын
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
@sanjaylakhani1050
2 жыл бұрын
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
@pratikpatade7844
5 жыл бұрын
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.
@Vickytalks_everythn
5 жыл бұрын
hi LEILA, how do i do this if i need to include weekends and holidays.
@billy6686
4 жыл бұрын
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?
@isranim
5 жыл бұрын
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.
@miiihaaas
2 жыл бұрын
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 :)
@miiihaaas
2 жыл бұрын
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))
@nileshsawant2099
4 жыл бұрын
Is there any way to find out 7th working day, if employees does not have work off on consecutive days?
@oliviersaingrain8728
3 жыл бұрын
Hello,can someone help me ,i would like to use this ,but i only want exclude holidays,
@bbqmadeeasy9576
4 жыл бұрын
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?
@sarahsparks4997
2 жыл бұрын
How can you exclude the start date
@GymJockey67
5 жыл бұрын
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?
@shreejanshrestha1931
4 жыл бұрын
same as mine. Have u find the solution
@lubeganasser694
6 жыл бұрын
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?
@LeilaGharani
6 жыл бұрын
Hi - if you go to Excel options - under general, you have a drop-down for theme. Make your selection there...
@UbaidUllah-nj5et
6 жыл бұрын
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....
@LeilaGharani
6 жыл бұрын
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?
@UbaidUllah-nj5et
6 жыл бұрын
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???
@LeilaGharani
6 жыл бұрын
Hi Ubaid - Not sure about this... could you give an example of a full formula?
@robertakwasiadjei8223
7 жыл бұрын
Thanks a lot. However, what about the dashboard course? I sent a mail but have not received any feedback
@LeilaGharani
7 жыл бұрын
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?
@hamzaijaz6850
6 жыл бұрын
What if starting date and end date is same for a task?
@Mrakhent
3 жыл бұрын
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?
@Mrakhent
3 жыл бұрын
I have found it=WORKDAY.INTL(DATE(2017;5;1); -1;1;$G$3:$G$17)
@salinneupane7120
2 жыл бұрын
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?
@ashishmanwatkar1074
5 жыл бұрын
How to get any Sunday date. ex.get date of 4th Sunday of September 2019
@AniManuSCh
5 жыл бұрын
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
@AniManuSCh
5 жыл бұрын
Here I have my spreadsheet where I tried to find a solution: 1drv.ms/x/s!AtYtwUtCJl08g_9dmnVOZ1U0oA-nzw
@LeilaGharani
5 жыл бұрын
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...
@MrAshfaqahmed
6 жыл бұрын
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.
@alpeshdoshi7827
7 жыл бұрын
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...
@LeilaGharani
7 жыл бұрын
Hi Alpesh - the IF function would work well here....
@szpunar85
4 жыл бұрын
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...
@philipnapoles5637
4 жыл бұрын
When I followed the procedure, the end date became " #value!". How to fix this?
@ramidaoud3776
Жыл бұрын
how to fetch the holidays for free with webservice?
@psycho007
3 жыл бұрын
How can i contact u ma'm
@hernanperez912
5 жыл бұрын
❤️❤️❤️❤️❤️👌👏
@Joker-Leto
2 жыл бұрын
complicated ... to claculate repeat rows
@excellover7172
6 жыл бұрын
#value! error
@fkntwizted
6 жыл бұрын
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?
@LeilaGharani
6 жыл бұрын
You can use the weekday function to find out if you come across a saturday or sunday. Here are some videos that can help: kzitem.info/news/bejne/qGx_04Z4p2WUhKA and kzitem.info/news/bejne/uKJ5mn2so4F0gaQ
@chypie1991
6 жыл бұрын
just subtract one in your formula after the reference cell
@excellover7172
6 жыл бұрын
#value error is coming while applying this formula, please help
@LeilaGharani
6 жыл бұрын
Try downloading the workbook from the link provided in the description of the video and cross-check with your version.
Пікірлер: 142