Excel WORKDAY.INTL Function

 

Summary

The Excel WORKDAY.INTL function takes a date and returns the nearest working in the future or past based on an offset value you provide. Unlike the WORKDAY function, WORKDAY.INTL allows you to customize which days are considered weekends (non-working days). You can use WORKDAY.INTL function to calculate completion dates, ship dates etc. that take into account non-working days.



Purpose

Get date n working days in future or past Return value Next or previous working date based on inputs

Syntax

=WORKDAY.INTL (start_date, days, [weekend], [holidays])

Arguments

·       start_date - The start date.

·       days - The end date.

·       weekend - [optional] Setting for which days of the week should be considered weekends.

·       holidays - [optional] A list of one or more dates that should be considered non-work days.

Usage notes

WORKDAY.INTL figures out a date that represents the "nearest" working day N days in the past or future. Use a positive number as days for future dates and a negative number for past dates. This function is more robust than the WORKDAY function because it lets you customize which days of the week are considered weekends. Example formulas

D4=WORKDAY.INTL(B4,C4) // default - weekends excluded D5=WORKDAY.INTL(B5,C5,11) // weekend set to Sunday only

D6=WORKDAY.INTL(B6,C6,1,B9:B9) // default weekend with holidays

Weekends

By default, WORKDAY.INTL will exclude weekends (Saturday and Sunday). However, you can control which days are considered weekends by supplying a code from the table below for the weekend argument. Another easier way to specify weekend days is to use a "mask" to indicate weekends with ones and zeros. In this scheme a string of 7 ones and zeros are provided to indicate weekends were the first character is Monday and the last character is Sunday. Use one (1) to indicate weekend and zero (0) to indicate a working day. This method is more flexible since it allows you to designate any day of the week as a weekend i.e. non-working day.

For example:

=WORKDAY.INTL(A1,3,"0000000") // no weekends

=WORKDAY.INTL(A1,3,"1000000") // weekend = Mon =WORKDAY.INTL(A1,3,"1100000") // weekend = Mon+Tue =WORKDAY.INTL(A1,3,"1110000") // weekend = Mon+Tue+Wed =WORKDAY.INTL(A1,3,"1010000") // weekend = Mon+Wed

Holidays

WORKDAY.INTL can also optionally take into account holidays. For the holidays argument, supply a range that contains holiday dates. These dates are treated as non-working days and will not be included in the result. Weekend codes Use any of the codes below for the weekend argument to select a "fixed" weekend option.

 

Code

Weekend days

1 (default)

Saturday, Sunday

2

Sunday, Monday

3

Monday, Tuesday

4

Tuesday, Wednesday

5

Wednesday, Thursday

6

Thursday, Friday

7

Friday, Saturday

11

Sunday only

12

Monday only

13

Tuesday only

14

Wednesday only

15

Thursday only

16

Friday only

17

Saturday only

Notes:

·       If start_date is invalid, WORKDAY.INTL returns the #NUM! error.

·       If start_date + day is invalid, WORKDAY.INTL returns the #NUM! error.

·       If any holiday is invalid, WORKDAY.INTL returns the #NUM! error.

·       If weekend is invalid, WORKDAY.INTL returns the #VALUE! error.

Last n days

Explanation

To check if a date is within the last n days of today's date you can use a formula based on the TODAY and AND functions when both results are TRUE, the AND function will return TRUE. If either result is FALSE, the AND function will return FALSE.

Without future checks

The second test is meant to exclude any dates greater than or equal to today. This test only makes sense if data may include dates in the future.


Recommended Articles






If Function








Comments

Post a Comment

Popular posts from this blog

TDS on Salary – Entry in Tally

Image
  Introduction TDS is Tax Deducted at Source. TDS is the amount of tax which is deducted by the employer from the taxpayer and is deposited to the Income Tax Department on behalf of him/her. The TDS rates are set on the basis of the age and income of different individuals. TDS or Tax Deducted at Source is a specific amount that is reduced when a certain payment like salary, commission, rent, interest, professional fees etc. is made. The person who makes the payment deducts tax at the source while the person who receives a payment/income has the liability to pay tax. It lowers tax evasion because the tax will be collected at the time of making a payment. Note: TDS is nothing but advance payment of income tax. When the TDS applied When you get your salary, TDS is deducted from your salary. Let’s for simplicity assume that the TDS on your salary is ₹20,000 and you are applying for the refund of the TDS because your income i.e. your salary is under the base limit for income tax wh
Read more

Post Dated Cheque (PDC) Management

Image
  Introduction Post Dated Cheque is an essential part of today’s business world. For a better financial planning and fund management PDC cheque is a must tool to survive in a tight financial conditions of a competitive world. A cheque in which a future date is written on its face is called Post Dated Cheque or popularly known as PDC. PDC can be issued to your suppliers and can be received from your customers. Normally Post-dated cheques are presented on the date of Cheque and booked or accounted on the same date. If you have a large number of PDC to manage then it is annoying for accountant to keep track of cheques. Tally ERP 9 take charge of the PDC Management for you and let you do a tension free accounting. You can enter post-dated cheques into receipt voucher, payment voucher and contra vouchers. Benefits of Post-dated Cheque Transactions 1.    You can enter the cheque into tally immediately after receiving or issuing it. 2.    You can mark a cheque as post-dated which wi
Read more

Personal Accounts in Tally

Image
 Introduction You can easily maintain personal accounts in Tally. To maintain personal accounts in Tally is very easy and simple. It is much faster than maintaining accounts for a company. The reason is that personal accounts are comparatively very small and involve only a few amounts of entries. Now compare that to a fully-fledged company which is selling products and includes all kinds of accounting entries and on the top of that, there is GST accounting entries too. But when you maintain personal accounts in Tally, there is generally no GST involved and you need to just focus on your balance sheet. So, let’s get started step by step. Maintain Personal Accounts in Tally When you have to maintain personal accounts in Tally, you should mainly focus on balance sheet. There is a specific reason for that. In personal account, there is no business account involved because it is your personal accounts. Therefore, there is NO need for a Profit and Loss Account. If you have a business, It
Read more