Excel adding working days only
16-01-2012, 10:52
|
#1
|
|
cf.mega poster
Join Date: Jan 2004
Age: 50
Posts: 1,998
|
Excel adding working days only
Hi all
I have a column of dates and would l add 20 working days(excluding weekends) to each date.
Is there a formula to do this ???
Many thanks for your time
Regards
Mike
|
|
|
|
16-01-2012, 11:03
|
#2
|
|
Cable Forum Team
Join Date: Jun 2006
Services: Triple XL (BB 30Mb), TiVo, V+
Posts: 22,887
|
Re: Excel adding working days only
Try the "WORKDAY" function.
=WORKDAY(A1,20) - where A1 is the cell with the initial date in it.
It is on the "Formulas" tab, then select "Date & Time", then "WORKDAY"
__________________
Just to make it clear if a post is bold and is from a team member, it's a moderating decision. If it's not bold or not from a team member, it's not.
|
|
|
|
|
16-01-2012, 11:32
|
#3
|
|
cf.mega poster
Join Date: Jan 2004
Age: 50
Posts: 1,998
|
Re: Excel adding working days only
Quote:
Originally Posted by Hugh
Try the "WORKDAY" function.
=WORKDAY(A1,20) - where A1 is the cell with the initial date in it.
It is on the "Formulas" tab, then select "Date & Time", then "WORKDAY"
|
Many thanks for this......
Tried it and it comes up #Name?
Can not find Formulas tab......
Can you please advise....
Many thanks
Mike
|
|
|
|
|
16-01-2012, 12:06
|
#4
|
|
cf.addict
Join Date: Sep 2006
Location: Nottingham
Services: Virgin TV XL, Broadband XL (60), Phone XL, Windows 7 Media Centre
Posts: 175
|
Re: Excel adding working days only
Depends on which version of Excel you have.
2003 needs the Analysis ToolPak installing first (from Tools/Add-Ins). 2010 just has it built in.
|
|
|
|
|
16-01-2012, 13:25
|
#5
|
|
cf.geek
Join Date: Dec 2007
Posts: 855
|
Re: Excel adding working days only
select the contents of the row/column, then click in the bottom right hand corner of the selection box, and you can set the dates to be workdays only.
|
|
|
|
|
16-01-2012, 19:05
|
#6
|
|
Double-Choc
Join Date: Mar 2004
Location: Brighton
Posts: 2,365
|
Re: Excel adding working days only
Quote:
Originally Posted by admars
select the contents of the row/column, then click in the bottom right hand corner of the selection box, and you can set the dates to be workdays only.
|
You mean the autofill? That just allows you to fill down with incrementing weekdays only.
Hugh's function is the right one, though you may want to add the bank holidays in to the exception list. Put the bank holidays in a list anywhere, even a different worksheet in the same workbook, and name the range BankHols then use this:
Code:
=WORKDAY(A1,20,BankHols)
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT +1. The time now is 16:50.
|
Join CF
You are here: 
