1 00:00:00,810 --> 00:00:06,210 Hello that they're starting to go to learn something which is very very important for project management 2 00:00:06,570 --> 00:00:14,250 managing and calculating deadline dates and also to find out that if a certain project starts on a defined 3 00:00:14,250 --> 00:00:20,040 beat predefined date and there are seven to eight or maybe x number of business these allocated to it 4 00:00:20,580 --> 00:00:24,160 by when shall it be estimated to get complete. 5 00:00:24,240 --> 00:00:29,240 And mind you when I say business does it can mean Monday to Friday. 6 00:00:29,280 --> 00:00:32,970 It can also mean Monday to Saturday and in different countries. 7 00:00:32,970 --> 00:00:37,090 Friday being a holiday it can also mean Saturday to Thursday. 8 00:00:37,200 --> 00:00:40,690 So let's see how to get hold of this formula. 9 00:00:41,010 --> 00:00:46,610 Firstly I've populated a very small list of dates and I mentioned the day that it belongs to example's 10 00:00:46,620 --> 00:00:50,550 38 December 2011 happens to be a Friday. 11 00:00:50,550 --> 00:00:58,010 Now the project start date is 30th December to 11 and I've dedicated five business days to complete 12 00:00:58,010 --> 00:00:58,470 it. 13 00:00:58,740 --> 00:01:05,700 But also notice that I have one more variable added that is among the group of the second January 2012 14 00:01:05,890 --> 00:01:07,550 is the holiday. 15 00:01:07,980 --> 00:01:10,040 So now how do I calculate that. 16 00:01:10,050 --> 00:01:11,770 When was the project going to end. 17 00:01:11,880 --> 00:01:17,530 Assuming that I do not take into consideration those starting the that is 30 December. 18 00:01:17,640 --> 00:01:19,270 When was the project get completed. 19 00:01:19,380 --> 00:01:26,580 Well let's find out if the business works with Saturday and Sunday or the holiday the next working day 20 00:01:26,580 --> 00:01:30,040 which we think as Monday in January is dead. 21 00:01:30,090 --> 00:01:31,510 But that's also holiday. 22 00:01:31,710 --> 00:01:40,710 So first the is toward next 4th next 5th and next the sixth and seventh and it's our weekend the next 23 00:01:40,710 --> 00:01:41,510 ending it happens. 24 00:01:41,520 --> 00:01:44,440 We want 9th of January 2012. 25 00:01:44,790 --> 00:01:46,960 Now how do I calculate this using the formula. 26 00:01:47,070 --> 00:01:48,120 Let's see. 27 00:01:48,120 --> 00:01:51,920 So let me dive in equal to W O R K. 28 00:01:52,200 --> 00:01:55,490 And what I get to see is to form allow work day and what they don't. 29 00:01:55,530 --> 00:02:02,430 I mean if you are using words in 2007 then you will be getting only one formula that would be not the 30 00:02:02,440 --> 00:02:03,880 would be ideal. 31 00:02:04,020 --> 00:02:09,270 The second formula which we are going to use right now has been introduced in version 2010 and this 32 00:02:09,270 --> 00:02:12,310 is something which is more powerful than the previous one. 33 00:02:12,510 --> 00:02:18,080 So present Dabi now it asks me starting date which is 30th December 2011. 34 00:02:18,240 --> 00:02:18,960 OK. 35 00:02:19,210 --> 00:02:27,150 Coma then it does me please choose the DS which I gladly choose as 5 and then it asks me what is the 36 00:02:27,150 --> 00:02:29,850 week in Boston that you follow. 37 00:02:29,910 --> 00:02:34,180 I would say I follow the normal one that is one Saturday Sunday. 38 00:02:34,290 --> 00:02:40,410 But yes in case you your country happens to fall only Monday to Saturday as business does that I will 39 00:02:40,410 --> 00:02:42,830 have to in 11 inductees in this case. 40 00:02:42,840 --> 00:02:46,870 Let me start with one now mind you there is one loop on the left. 41 00:02:46,870 --> 00:02:53,500 It says what a list of holidays I dont need to choose the columns of holiday and the type of holiday 42 00:02:53,500 --> 00:02:56,760 I just need to use the cells which contain the date of holidays. 43 00:02:56,890 --> 00:02:58,420 Thats about it. 44 00:02:58,420 --> 00:03:01,020 And if I close the bracket let me see what I get. 45 00:03:01,060 --> 00:03:07,140 I get 9 of January 2008 and this is the thing that we had initially thought of as an anthem. 46 00:03:07,150 --> 00:03:11,800 Now imagine if sick in January 2012 was not a holiday. 47 00:03:11,870 --> 00:03:18,270 I believe that the moment I didn't notice since it is not a holiday it's considered as a working day. 48 00:03:18,450 --> 00:03:19,150 And because of it. 49 00:03:19,150 --> 00:03:24,680 One two three four five five days gets completed on which the 6th of January 2012. 50 00:03:25,200 --> 00:03:30,420 Imagine that this particular calculation needs to be done in a manner where Sunday is considered to 51 00:03:30,420 --> 00:03:32,330 be a hundred day Saturday of business day. 52 00:03:32,400 --> 00:03:33,230 So what do I do. 53 00:03:33,950 --> 00:03:35,880 I never click on the formula. 54 00:03:36,110 --> 00:03:37,960 I grew Inter-Parliamentary to begin. 55 00:03:37,980 --> 00:03:39,390 Find them again. 56 00:03:39,650 --> 00:03:44,540 I Backspacer it and I'm going to choose 11 and I press enter. 57 00:03:44,840 --> 00:03:47,990 Notice this time it has further reduced by one day. 58 00:03:48,110 --> 00:03:55,520 That means telephone December is the working day second third fourth and fifth sixth and nine normal 59 00:03:55,520 --> 00:03:56,600 needed. 60 00:03:56,600 --> 00:04:04,040 So this is how you calculate in Bede's deadline ladies and when a product needs to be get completed 61 00:04:04,620 --> 00:04:07,250 and quite often it happens the window closes. 62 00:04:07,340 --> 00:04:13,490 There are service agreements that says the resolution must happen in 5 business days or six business 63 00:04:13,490 --> 00:04:19,970 days in which case this could also be used as a control mechanism to find out by then by which date 64 00:04:20,180 --> 00:04:22,900 the resolution has had to be completed. 65 00:04:23,030 --> 00:04:24,580 That was Will be done. 66 00:04:24,610 --> 00:04:26,500 I intill formula for you. 67 00:04:26,600 --> 00:04:32,750 Just keep in mind that this in Clunes all the dates except the project started. 68 00:04:32,750 --> 00:04:41,990 So if you want to make that minor adjustment you can inside the formula by saying plus one that means 69 00:04:42,170 --> 00:04:46,980 sixth of January or in this case minus 1. 70 00:04:47,230 --> 00:04:50,800 So that plus one minus one is you can do that based on your calculation.