Have control of days calculated
In my last article (August 02, 2021) I did discussed a better way of calculating days for business, where you could exclude weekends, and holidays, depending on how you wanted to calculate dates. This is more commonly used of deadlines, be it for credit, or supply of goods.
But with the use of this formula you do not have that much of control over the calculation, as the formula will omit the weekends (Saturday and Sunday) and any days that you mark as holidays.
Now what is your company is not working on only Sunday, or what if the company is closed on Sunday and Monday.
Well now you can, with the use of an advance date function. The function is known as “WORKDAY.INTL”. The function is a much stronger function, and is useful for business in calculating due dates, for delivery dates, etc.
The reason being that unlike the WORKDAY function here you do have the freedom to managing and defining your own weekends. The function is ideal for business that consider
- Business that work on Saturday and Sunday, and non-working in Monday and Tuesday
- Business that work on specific days of the week such as Monday, Wednesday and Friday.
- Business that work for 6 days of the week, instead of 5.
The syntax for the function is =WORKDAY.INTL(start_date,days,[weekend],[holidays])
start_date — Refers to the date which you intend to start the production if your a a company in to production of goods, and if in finance where invoicing is concerned the date of invoice.
day — refers to the the number of days required for the production, or number of days that your giving as credit.
weekend-refers to the days defined by us as the days that need to be considered where the business in not in operation. This we have two options that could be used, one is where we could use a set of predetermined numbers that Microsoft has given along with excel considering possible non working days, and the second being an option where you have more freedom, what days are non working.
holidays — is an optional part, of the function. Here you can define dates where the company is not in operation apart from weekends, and these would be excluded in calculating the final out put date or the due date.
The function is available to those using Excel 2010 or above, and as such those using a lower version I suggest that you upgrade.
Before we use the function lets look at what are the predefined numbers are, that could be used.
The non working days predefined by Microsoft is as indicated in the table in the right.
These numbers can be used in the function, as long as we are in an organization that does not work on days as indicated in the table.
However, what if the nonworking days are not in an order as indicated in the table. What if the nonworking days in your business model is Monday, Thursday, and Sunday? In such a situation we would not be able to use the numbers as indicated in the above table.
With situations such as indicated where nonworking days are different to the table, we could use a string of numbers with a combination of “0” and “1”, totaling to seven digits within inverted comas. The seven digits represent the seven days of the week as Monday to Sunday, with the first digit representing Monday and last representing Sunday. In a situations where the nonworking days are Monday, Thursday and Sunday, all you need to do is combine “0” and “1”, with “0” indicating the working days, and “1” indicating the nonworking says. Thus the string of digits for the example take would be “1001001” (Refer the illustration number representation).
In order to understand how the function works, lets take an example. Lets assume that a production company who produces a certain product type, and delivers to the market on order. Let’s assume production division works 5 days of the week with nonworking days being mercantile holidays, and Mondays and Fridays. Whilst the delivery division only delivers goods on Tuesday, Wednesday, Thursday (excluding any mercantile holidays that fall during the days of delivery)
Lets also assume that it takes 6 working days (excluding the day of order) to produce and order, and it takes 2 working days (excluding the day of completion) to deliver the goods from the date of completion of production. Lets also assume that the orders given are as per the table under caption “order table”.
The first thing to do would be to define the number days required to complete the order and deliver. The table once these have been also included would be similar to the table given below.
Now lets create an additional table next to this to identify when would the production be completed and when would the goods be delivered. Lets call this part “Target Date”. Now the table should look like the one below.
All we have to do is use the WORKDAY.INTL function and calculate the date when the production would be completed, and when would the goods be delivered. To start with we would need to type the function in cell “ H3”, and then this could be copied down. In cell H3, the function should be as =WORKDAY.INTL(A3,E6,“1000100”). A3 is the cell representing the date which needs to be considered as the “start_date”, and E6 is the cell where we have indicated how many days it take to complete the production which needs to be considered as the “days” in the function. The next part “1000100” is where we have defined Monday and Friday as the nonworking days for the production department.
Once you have entered the function to the cell, lets move the cell I3 to calculate the date when the goods would be delivered to the customer. Here we would need to type =WORKDAY.INTL(H3,F3,“1000111”). Cell H3 is where the date when the production would be completed, and this would be the date that should be referred to as “start_date”, and F3 represents the number of days that need to be considered as “days” in the function. “1000111” refers to the days when the delivery would be done, as deliveries are done on Tuesdays, Wednesdays and Thursdays only.
Now all you need to do is to copy the functions down on both cell H3 and I3 and you table should now look like the one below.
Lets assume that there were few mercantile holidays that fell during the time period as per the table indicated under the caption “mercantile holidays”. How will the function be changed?
All you need to do is introduce the range of days that are falling as mercantile holidays to the function. Now the function in cell H3 would be as =WORKDAY.INTL(A3,E6,“1000100”,$O$2:$O$5), and function in cell I3 would be =WORKDAY.INTL(H3,F3,“1000111”,$O$2:$O$5). The “O2:O5” is the range of days that need to be considered as mercantile holidays.
You would also notice that this range in the function included dollar sign “$”, which is where I have locked the range with absolute cell referencing, to ensure that the range remains unchanged as you copy the function down.
Now the table should look like the one given below, and notice how the dates have changed.
Now try this with you own data and see whether this would help in your day to day operations.
Happy excelling to all.