Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

In performing calculations with Excel, it is often helpful to know how many days there are between two dates. Excel makes this easy—you just subtract the earlier date from the latter.

In a business environment, however, you may not want to know just the number of days—you probably want to know the number of business days between two dates. In other words, how many workdays are there between two dates?

Believe it or not, Excel makes it almost as easy to calculate business days as it is to calculate regular days. All you need to do is use the NETWORKDAYS worksheet function. This function is not intrinsic to Excel; it is part of the Analysis ToolPak. (How you enable the Analysis ToolPak is discussed in other *ExcelTips.*)

Let's suppose for a moment that you had two dates: one in A3 and the other in A4. The date in A3 is your starting date and the date in A4 is the ending date. To calculate the work days between the two dates, you could use the following formula:

=NETWORKDAYS(A3,A4)

This returns a count of all the days between the two dates, not counting weekends. You should note that the function returns the number of full days. Thus, if your starting date was Sept. 4 and your ending date was Sept. 5, the function would return a value of 2. (Provided neither day was a weekend day.)

If you want to account for holidays, the easiest way is to enter your standard holidays in a range of cells, and then define a name for that range. (I always like the terribly obvious name of "Holidays.") You can then alter the NETWORKDAYS formula in this manner:

=NETWORKDAYS(A3,A4,Holidays)

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (2155) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Calculating Business Days.

*Related Tips:*

**Professional Development Guidance!** Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out *Professional Excel Development* today!

Hi I am working on a storage calculation. I have product being stored on day A, then I have 14 days free of charge and as from day 15 I can charge customer. Now I would like to have an overview of the payable days per month. Meaning if on Jan 25 I would store goods, there will be no invoice in Jan for these goods, but in Feb this would mean on the 29 I would be able to charge (31-25 =6 => 14-6 = 8) as of 8 feb till end of month, in this case 29th for this month.

Thx in advance.

Thx in advance.

need your help if possible.

I create an excel worksheet for storing good , under date in i keep it free

in the cell of date out I put the following formula to stay days current =TODAY() to calculate the duration I used =DATEDIF(E5,F5,"D")

after release the goods from the store I need to use a formula to stop counting the date can you please help on this

thanks in advance for your help

I create an excel worksheet for storing good , under date in i keep it free

in the cell of date out I put the following formula to stay days current =TODAY() to calculate the duration I used =DATEDIF(E5,F5,"D")

after release the goods from the store I need to use a formula to stop counting the date can you please help on this

thanks in advance for your help

how to find the days count in between two date , eg from 27-Jan-2016 to 03-Mar-2016, how many days. Please advise the formula to use in excel file.

Thanks

Best regards

Ali Anwar

Thanks

Best regards

Ali Anwar

Dear Friend,

i need help to find this solution.

starting day plus 30 days excluding 2 days per week.

total how many days.

please help me out.

Thanks

i need help to find this solution.

starting day plus 30 days excluding 2 days per week.

total how many days.

please help me out.

Thanks

@McKinley Darden,

If I understood you correctly - try my suggestion in the linked picture.

You may alter the number of days (presented in red font).

If I managed to help - I wish you all the best in your new job.

http://screenpresso.com/=t2IQc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

If I understood you correctly - try my suggestion in the linked picture.

You may alter the number of days (presented in red font).

If I managed to help - I wish you all the best in your new job.

http://screenpresso.com/=t2IQc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hello,

I appreciate your insight. Very helpful.

I am working for a home loan institution and I have been tasked to create a spreadsheet that will allow us to calculate the estimated closing date of a loan according to business days.

We have the start date(date of application), but here's where it can get tricky. Each portion of the loan process can last a different number of days. For instance gathering the initial documents takes 4-7 days depending on the customer. Then the paperwork is submitted which is time variable as well. There are about 15 stages to the loan process which we are trying to represent in the sheet.

Ideally we would like to be able to enter a start date, enter an ideal close date, then enter each of the loan stage names and a number of days each ideally takes, and equal a closing date.

If one stage gets delayed, we would like to be able to change the # of days and have it automatically change the closing date.

If I can get this right it will create a job for me at this institution. Please help!

McKinley Darden

I appreciate your insight. Very helpful.

I am working for a home loan institution and I have been tasked to create a spreadsheet that will allow us to calculate the estimated closing date of a loan according to business days.

We have the start date(date of application), but here's where it can get tricky. Each portion of the loan process can last a different number of days. For instance gathering the initial documents takes 4-7 days depending on the customer. Then the paperwork is submitted which is time variable as well. There are about 15 stages to the loan process which we are trying to represent in the sheet.

Ideally we would like to be able to enter a start date, enter an ideal close date, then enter each of the loan stage names and a number of days each ideally takes, and equal a closing date.

If one stage gets delayed, we would like to be able to change the # of days and have it automatically change the closing date.

If I can get this right it will create a job for me at this institution. Please help!

McKinley Darden

Hello,

I need a help..

I have time logged between two dates, for eg

1 Nov 2015 4 PM to 2 Nov 2015 4 AM, now the time recorded for this is say 5hrs and 2 hours respectively. this gets recorded in two dates mentioned, however i want it in 1 itself, as my working day 4 PM to 4 AM..

How to do this in excel..

Urgent pls

I need a help..

I have time logged between two dates, for eg

1 Nov 2015 4 PM to 2 Nov 2015 4 AM, now the time recorded for this is say 5hrs and 2 hours respectively. this gets recorded in two dates mentioned, however i want it in 1 itself, as my working day 4 PM to 4 AM..

How to do this in excel..

Urgent pls

I came across an issue counting Working Days or Business Days which appears to be related to & only effecting the month of Dec after claendar year 2015 (counts for Working Days in Dec 2014 are correct).

There should be 23 Working Days in Dec 2015, but all of a sudden my formula calcs as 24 (was correct in prior months & years). Here is my formula:

=NETWORKDAYS(EOMONTH(todaysDate,-1),EOMONTH(todaysDate,0))

Am i doing something wrong or is this a new/known issue?

TIA

There should be 23 Working Days in Dec 2015, but all of a sudden my formula calcs as 24 (was correct in prior months & years). Here is my formula:

=NETWORKDAYS(EOMONTH(todaysDate,-1),EOMONTH(todaysDate,0))

Am i doing something wrong or is this a new/known issue?

TIA

@John Finn,

A much shorter approach:

http://screenpresso.com/=rqVI

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

A much shorter approach:

http://screenpresso.com/=rqVI

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

@John Finn,

Try my suggested approach in the linked picture:

http://screenpresso.com/=8pICg

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Try my suggested approach in the linked picture:

http://screenpresso.com/=8pICg

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Can you tell me how to create a formulae that takes a given date adds 28 days inc weekends and holidays and then returns a date that is the next Monday's date past the 28 day period please

I.e. Wednesday 4 November is the start date

28 days later would be 2 December, however I need a deadline date to display the following monday.(7 December).Unless the 28th day fell on a Monday...welcome to my dilemma!

I.e. Wednesday 4 November is the start date

28 days later would be 2 December, however I need a deadline date to display the following monday.(7 December).Unless the 28th day fell on a Monday...welcome to my dilemma!

@Atiqullah Raheel,

Try: =NETWORKDAYS.INTL(Start_Date, End_Date,7)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Try: =NETWORKDAYS.INTL(Start_Date, End_Date,7)

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Thanks for the article. How to define weekend days in this formula. In our case Friday and Saturday is weekend days, not Saturday and Sunday.

Tanks,

Atiq

Tanks,

Atiq

@Melany,

Try the suggested formulas from the linked picture:

http://screenpresso.com/=3NYIc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Try the suggested formulas from the linked picture:

http://screenpresso.com/=3NYIc

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Hi!

Thank you for the article! In a range of dates I need to know how many days there are in one month and how many days in the following month!

Example:

30/05/2015-13/06/2015 : 2 days in May and 12 days in June.

Is it possible? Thank you ;)

Thank you for the article! In a range of dates I need to know how many days there are in one month and how many days in the following month!

Example:

30/05/2015-13/06/2015 : 2 days in May and 12 days in June.

Is it possible? Thank you ;)

Im trying to identify dates. I need a formula thatI can plug a start date and it calculates 55 working dates (no weekends or holidays) and it gives me that future date.

Thanks

Thanks

I work in a complaints department and I am trying to calculate the average number of working days to close a complaint. I have so far calculated the average number of working days for each complaint closed in the month of July but now need to calculate the overall average of this (for the whole month).. Please help?

@Louis,

From Excel version 2010 and on - all you need is a simple formula like hereunder.

=NETWORKDAYS.INTL(Start_date,End_date,16)

It will calculate the amount of days (EXCLUDE Fridays) between to dates.

----------------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

From Excel version 2010 and on - all you need is a simple formula like hereunder.

=NETWORKDAYS.INTL(Start_date,End_date,16)

It will calculate the amount of days (EXCLUDE Fridays) between to dates.

----------------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

I would like to calculate remaining days between to dates and then remove friday. Formula please?

hi

i need to Occupancy%

for example no of cabs 66

no of employees 338,

what is the Occupancy%

pls help me

i need to Occupancy%

for example no of cabs 66

no of employees 338,

what is the Occupancy%

pls help me

its like this

=networkdays(A1,B1,RANE X:Y)

A1 =start date

B1 =finish date

RANGE X:Y = range of selected cell with holidays added in it

hope this will be useful to a few :)

=networkdays(A1,B1,RANE X:Y)

A1 =start date

B1 =finish date

RANGE X:Y = range of selected cell with holidays added in it

hope this will be useful to a few :)

I am trying to create a duty roster for weekends and holidays, what formula should I use?

In MS Excel 2007:

How can I calculate total days required to do a job considering the below conditions:

> if the start date of the job is 01-Jan-15 (suppose)

> End Date: 30-jun-15 (suppose)

> only one day (Friday) as a weekly holiday

> other govt. holiday (as per govt. circular mentioning somewhere in the sheet manually)?

Please help.

How can I calculate total days required to do a job considering the below conditions:

> if the start date of the job is 01-Jan-15 (suppose)

> End Date: 30-jun-15 (suppose)

> only one day (Friday) as a weekly holiday

> other govt. holiday (as per govt. circular mentioning somewhere in the sheet manually)?

Please help.

OK I need some help here. I have been trying to figure this one out and cant seem to get it. I need to calculate the time is takes for one of my agents to close a ticket that has been assigned to them. This needs to exclude holidays and weekends. My dates and times look like this:

Column O (opened) Column P (Closed)

2/27/2015 4:34:00 AM 3/10/2015 5:45:00 PM

(The time and date are in the same cell)

Here are the formulas that I have tried but none of them seem to be getting it right.

=(NETWORKDAYS(O2,P2)-1)*5/12+MOD(P2,1)-MOD(O2,1)

=NETWORKDAYS(O2,P2,Holidays!$A$2:$A$14)

=NETWORKDAYS(O2,P2)-1

Can anyone help??

Column O (opened) Column P (Closed)

2/27/2015 4:34:00 AM 3/10/2015 5:45:00 PM

(The time and date are in the same cell)

Here are the formulas that I have tried but none of them seem to be getting it right.

=(NETWORKDAYS(O2,P2)-1)*5/12+MOD(P2,1)-MOD(O2,1)

=NETWORKDAYS(O2,P2,Holidays!$A$2:$A$14)

=NETWORKDAYS(O2,P2)-1

Can anyone help??

Hi. Please help me with this:

Company A service hour:

Mondays-Saturdays, 7am-5pm

Company B service hour:

Mondays-Friday, 8am-5pm

SLA of Ticket per Priority:

Priority 1 - 4hours

Priority 2 - 8 hours

Priority 3 - 36 hours

Priority 4 - 72 hours

What will be the general formula when a ticket is logged within and beyond the service period per priority?

Thanks in advance for the help!

Company A service hour:

Mondays-Saturdays, 7am-5pm

Company B service hour:

Mondays-Friday, 8am-5pm

SLA of Ticket per Priority:

Priority 1 - 4hours

Priority 2 - 8 hours

Priority 3 - 36 hours

Priority 4 - 72 hours

What will be the general formula when a ticket is logged within and beyond the service period per priority?

Thanks in advance for the help!

Hi

I need help.

I have a formula in Excel that calculates each day someone is off sick from work and stops counting once you enter a return to work date.

However, I need the formula to discount weekends.

Now I'm aware of the "=NETWORKDAYS.INTL..." function.

The current formula I have enables me to calculate the number of days someone is off sick and effectively stops the clock once you enter a return to work date.

So what formula/function calculates the number of days someone is off sick, stops the clock once you enter a return to work date but also excludes weekends?

Kind Regards

Vividon80

I need help.

I have a formula in Excel that calculates each day someone is off sick from work and stops counting once you enter a return to work date.

However, I need the formula to discount weekends.

Now I'm aware of the "=NETWORKDAYS.INTL..." function.

The current formula I have enables me to calculate the number of days someone is off sick and effectively stops the clock once you enter a return to work date.

So what formula/function calculates the number of days someone is off sick, stops the clock once you enter a return to work date but also excludes weekends?

Kind Regards

Vividon80

Hi. Excel master. I have a problem here.,

I want to know the 2 dates i Inputed.

This is the situation

I have 2 dates, the received date and the dispatch date.

I can subtract the two dates but what i want to know is " If no input dates or blank on the cell = the present date.

I hope someone can help me.

Thanks

I want to know the 2 dates i Inputed.

This is the situation

I have 2 dates, the received date and the dispatch date.

I can subtract the two dates but what i want to know is " If no input dates or blank on the cell = the present date.

I hope someone can help me.

Thanks

I found it - i had to add the -1 to the end of the function.

I need to calculate the difference between business days.

Cell A1 = 3/3/15

Cell B1 = 3/3/15

Cell C1 = NETWORKDAYS(A1,B1)

Cell C1 then populates with "1". When in reality it should be "0".

What is the function I can use to calculate the difference between business days?

Cell A1 = 3/3/15

Cell B1 = 3/3/15

Cell C1 = NETWORKDAYS(A1,B1)

Cell C1 then populates with "1". When in reality it should be "0".

What is the function I can use to calculate the difference between business days?

Dear All,

I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013

Thanks to all who can help me with this in advance :)

Regards,

Flava

I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013

Thanks to all who can help me with this in advance :)

Regards,

Flava

@jc,

Assuming business days are Mon-Fri and your Excel version is earlier than 2010 and the 1st. Feb is in cells A1 and the 18th. Feb. in Cell B1, - try:

=NETWORKDAYS(A1,B1)

*** For this Function to work in old versions of Excel - you need to enable the "Analysis ToolPak" Add-In.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Assuming business days are Mon-Fri and your Excel version is earlier than 2010 and the 1st. Feb is in cells A1 and the 18th. Feb. in Cell B1, - try:

=NETWORKDAYS(A1,B1)

*** For this Function to work in old versions of Excel - you need to enable the "Analysis ToolPak" Add-In.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

How do I get excel to give me the actual number of business days (for today it should give me 13 as a result, today = 2/18/15) Thank you

@Willy,

In 'Excel 2003' (and maybe even in 2000/2002) it works fine for me with all its arguments.

If you can't "standarditied" it - try

to enable the Analysis Toolpak add-ins.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

In 'Excel 2003' (and maybe even in 2000/2002) it works fine for me with all its arguments.

If you can't "standarditied" it - try

to enable the Analysis Toolpak add-ins.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

You can solve this with the WORKDAY function but it is only standard starting Excel 2007

Thank you very much (Micky) Avidan,

However, my logic is I want employees to input their Leave start date in one column, then I want Excel to calculate 30 workdays from the start date and output the end date of their leave in the next column automatically.

Please see if you can help me out. I have been trying and experimenting with the DATE functions but cqant get the solution :(

However, my logic is I want employees to input their Leave start date in one column, then I want Excel to calculate 30 workdays from the start date and output the end date of their leave in the next column automatically.

Please see if you can help me out. I have been trying and experimenting with the DATE functions but cqant get the solution :(

@Murtala,

The function: WORKDAY has a special argument called: Holidays

Check it out in Excel's help.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

The function: WORKDAY has a special argument called: Holidays

Check it out in Excel's help.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

HELLO,

THANK YOU ALL FOR THE GOOD INPUT.

I NEED A SOLUTION WHEREBY, I HAVE 30 WORKDAYS HOLIDAY, AND I HAVE A START DATE.

HOW DO I AUTOMATICALLY MAKE EXCEL CALCULATE 30 WORKDYAS FROM MY START DATE AND DISPLAY THE END DATE IN THE NEXT COLUMN.

YOUR SUPPORT WILL BE HIGHLY APPRECIATED.

THANK YOU ALL FOR THE GOOD INPUT.

I NEED A SOLUTION WHEREBY, I HAVE 30 WORKDAYS HOLIDAY, AND I HAVE A START DATE.

HOW DO I AUTOMATICALLY MAKE EXCEL CALCULATE 30 WORKDYAS FROM MY START DATE AND DISPLAY THE END DATE IN THE NEXT COLUMN.

YOUR SUPPORT WILL BE HIGHLY APPRECIATED.

@Coleen,

Assuming today's date resides in cell A1 - Try:

=WORKDAY(A1, 30)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Assuming today's date resides in cell A1 - Try:

=WORKDAY(A1, 30)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

I want a quick formula that can put in today's date and calculate what date it will be for business days. For example, I have a note that was done on 10/7/14 and I need to find out how to calculate what date it will be in 30 business days. I do not have a two date span. I want to find out what date it "will" be once I do the calculation. Thank you

@Erica,

Check Excels Help for both functions:

WORKDAY.INTL and WORKDAY

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Check Excels Help for both functions:

WORKDAY.INTL and WORKDAY

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

How do I calculate a due date that is 30 business days from the end date? Excluding holidays.

@Patti,

Assuming the start date is in cell A1 and the number of days to add in B1 - try:

=WORKDAY(A1,B1)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

Assuming the start date is in cell A1 and the number of days to add in B1 - try:

=WORKDAY(A1,B1)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

If I have a start date and the duration is 2 days (indicated by a whole number is an adjacent cell), what formula do I use to find the end date which COUNTS the start date as one day, excluding holidays and weekends. This is for creating a schedule, and want to be able to change the "Duration" whole number and have it automatically calculate the new end date. Or have a start date and calculate the start date for the next task based on the days from previous task start date including that start date as Day 1.

@CG,

Put: =TODAY() in the starting date cell or: TODAY() directly into the formula.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

Put: =TODAY() in the starting date cell or: TODAY() directly into the formula.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

@JKP,

if the "aware" date is in cell A1 and the amount of workdays to be added is in cell A2 -

type the following formula into cell A3: =WORKDAY(A1,A2)

and you should end up with the: March, 14, 2014.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

if the "aware" date is in cell A1 and the amount of workdays to be added is in cell A2 -

type the following formula into cell A3: =WORKDAY(A1,A2)

and you should end up with the: March, 14, 2014.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

How can I set up the formula to always look at today's date?

I am trying to add 30 business days to an "aware" date (in this case it's Jan 31 2014) to calucale a "due date". can you help me with the calculation?

Although this tip is intended for "97-2007" users - all "Modern" users should be aware that starting with "Excel 2010" there is an extended NetWorkdDays Function: NETWORKDAYS.INTL

The following formula calculates the working days' between two given dates, excluding Fri-Sat.

=NETWORKDAYS.INTL(A3,A4,7)

One should check "Excel" Help for, so called, excluded "weekends" options.

Michael Avidan

“Microsoft®” MVP – Excel

ISRAEL

The following formula calculates the working days' between two given dates, excluding Fri-Sat.

=NETWORKDAYS.INTL(A3,A4,7)

One should check "Excel" Help for, so called, excluded "weekends" options.

Michael Avidan

“Microsoft®” MVP – Excel

ISRAEL

I had a challenge because of a business process wrinkle. They wanted monthly SLA averages of ticket times, but only wanted to charge the help desk for time spent between 7:00am and 5:00pm.

The most direct solution I could devise was to use NETWORKDAYS to add the time for the first day, the time of the last day, and 10 x the count of the days in between.

NETWORKDAYS made it easy to eliminate weekends and holidays, but I had to devise error checking in the formula and limit one day responses to not include additional time.

The most direct solution I could devise was to use NETWORKDAYS to add the time for the first day, the time of the last day, and 10 x the count of the days in between.

NETWORKDAYS made it easy to eliminate weekends and holidays, but I had to devise error checking in the formula and limit one day responses to not include additional time.

Sara,

Add -1 to the end of the formula thus: =NETWORKDAYS(A3,A4)-1

Add -1 to the end of the formula thus: =NETWORKDAYS(A3,A4)-1

You said this function counts the number of full days "Thus, if your starting date was Sept. 4 and your ending date was Sept. 5, the function would return a value of 2"

Is there a function to use that would not count the start date as a day?

I work at a test lab, and I have to calculate how many days tests were started late. So if the start date was Sept. 4 and the end date was Sept. 5, I need to show that the test started one day late. Any advice will be helpful.

Is there a function to use that would not count the start date as a day?

I work at a test lab, and I have to calculate how many days tests were started late. So if the start date was Sept. 4 and the end date was Sept. 5, I need to show that the test started one day late. Any advice will be helpful.

If this tip does not work on your spreadsheet, you need to do the following:

Go to: Tools ---> Add Ins, then turn on Anaysis ToolPak.

After the Analysis ToolPak installs, re-type the formula and it should work just fine.

Go to: Tools ---> Add Ins, then turn on Anaysis ToolPak.

After the Analysis ToolPak installs, re-type the formula and it should work just fine.