0706 Date Formulas - WORKDAY.INTL() for Deadline/Due date Calculations w. Custom Weekends/Holidays

Advanced Excel Crash Course Section 7: Working with Dates
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

Hello there, they're starting to learn something which is very very important for project management, managing and calculating deadline dates. And also to find out that if a certain project starts on a defined date predefined date, and there are seven to eight or maybe X number of business these allocated to it by When shall it be estimated to get complete. And mind you, when I say business days, it can mean Monday to Friday. It can also mean Monday to Saturday, and in different countries Friday being a holiday, it can also mean Saturday to Thursday. So let's see how to get hold of this formula. Firstly, I have populated a very small list of dates as I mentioned the date that it belongs to example 30th December 2011 happens to be a Friday.

Now the project start date is 30th December 2011. And I've dedicated five business days to complete it. But also notice that I have One more variable added that is amongst the group of dates. Second, January 2012 is a holiday. So now how do I calculate that? When will the project going to end?

Assuming that I do not take into consideration the starting date that is 30th December? When will the project get completed? Well, let's find out. If the business works with Saturday and Sunday as a holiday, the next working day which we think as Monday, second January is there, but that's also holiday. So my first day is third, next, fourth, next, fifth, and next is sixth. And since seventh, and eighth are weakened, the next ending date happens to be what?

Ninth of January 2012. Now how do I calculate this using the formula? Let's see. So let me type in equal to w o Rk. And what I get to see is two formula workday and workday.io MTL, if you are using version 2007 then you will be Getting only one formula that is workday, not the workday dot IMT L. The second formula which we are going to use right now has been introduced in version 2010. And this is something which is more powerful than the previous one.

So I press the Tab key. Now it asks me starting date, which is 30th, December 2011. Okay, comma, then it tells me Please choose the days which I gladly choose as five. And then it asks me, what is the weekend pattern that you follow? I would say I follow the normal one that is one Saturday, Sunday. But yes, in case you your country happens to fall only Monday to Saturday as business days that I would have chosen 11 In that case, in this case, let me start with one.

Now mind you, there's one more parameter left it says what are your list of holidays? I don't need to choose the columns of holiday and the type of holiday I just need to choose the cells which contain the date of holidays. That's about it. And if I close the bracket, let me see what else I get ninth of January 2012. And this is the thing that we had initially thought of as an answer. Now imagine if second January 2010 was not a holiday.

I believe that the moment I delete that, notice since it is not a holiday, it's considered as a working day. And because of which 123455 days gets completed on which day sixth of January 2012. Imagine that this particular calculation needs to be done in a manner where Sunday is considered to be holiday Saturday, a business day. So what do I do? I double click on the formula. I go to the third parameter, it says we can define the weekend.

I backspace it and I'm going to choose 11 and I press enter. Notice this time it has further reduced by one day. That means 31st December is a working day, second, third, fourth and fifth, six and nine, no more needed. So this is how you calculate end dates, deadline date. And when a project needs to be get completed, and quite often it happens alone vendor clauses, there are service agreements which says the resolution must happen five business days or six business days. In which case, this can also be used as a control mechanism to find out by when by which date the resolution has had to be completed.

That was work date dot MTL formula for you. Just keep in mind that this includes all the dates except the project start date. So in case you want to make that manual adjustment, you can inside the formula by saying plus one that means sixth of January, or in this case, minus one. So that plus one minus one days you can do that based on your calculation.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.