Future date with six day week including holidays

can anyone help me with this
a. how to get future date (like WORKDAY Function) but the formula
should include saturdays as work day excluding sundays and holidays.

for example if 2 mar 10 is the starting date then plus 15 days
including 2 holidays (say 5 mar 10 & 9 mar 10) and sundays (i.e. 7 mar
& 14 mar) the to be arrived should be 20 mar 10.
and the formula should also check if the date arrived is a saturday
then it should add following sunday i.e in above case it should become
21 mar 10
:)




-- 
ajitexcel
0
ajitexcel
3/7/2010 10:40:16 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
803 Views

Similar Articles

[PageSpeed] 37

Hi

The following array entered formula is based upon one written by the 
late Frank Kabel.

It depends upon some defined names.
Use Insert>Name>Define>
Days		=cell with number of days to add
Start_date	=cell containing the start date
holidays	=range of cells containing holiday dates

Then use the array entered formula
{=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)*
10))),ABS(days)))}

Use Control+Shift+Enter (CSE) not just Enter when you save or amend the 
formula. Do not type the curly braces  {  } yourself, Excel will insert 
them when you use CSE.

I believe that you meant, when the result ends on a Saturday, use the 
following Monday, which would be the 22nd March, not the 21st.
--
Regards
Roger Govier

ajitexcel wrote:
> can anyone help me with this
> a. how to get future date (like WORKDAY Function) but the formula
> should include saturdays as work day excluding sundays and holidays.
> 
> for example if 2 mar 10 is the starting date then plus 15 days
> including 2 holidays (say 5 mar 10 & 9 mar 10) and sundays (i.e. 7 mar
> & 14 mar) the to be arrived should be 20 mar 10.
> and the formula should also check if the date arrived is a saturday
> then it should add following sunday i.e in above case it should become
> 21 mar 10
> :)
> 
> 
> 
> 
0
Roger
3/9/2010 10:25:09 AM
Reply:

Similar Artilces:

How do I correct date format?
Just downloaded bank statement in .csv format. When I load it into Excel the date column contains a number, 7011. When I try to convert this to something more meaningful, Format > Cell > Date and select my prefered format it comes back as 12/03/1919, when it should be 20/05/2006. Any suggestions as to how to fix this? Ooops! Operator error please ignore earlier posting. ...

Date and Time setup
I work at a 24 hour job we take readings every hour. I want to have the date autofill a cell as the current date but our sheet changes at 0700 every day. So if the sheet is worked on at 0100 the date needs to be for the past day starting at 0700, if the time for the current day changes to 0800 the cell will display the new date. ie: 0100hrs = 4/18/06 0600hrs = 4/18/06 0800hrs = 4/19/06 etc... =TODAY()-(A1<=TIME(7,0,0))*1 HTH -- AP "Joe" <jdbarnes63@earthlink.net> a �crit dans le message de news:28db731403ef4144bf9b3800591a428e@ureader.com... > I work at a 24 ho...

Problem with dates in a DAvg query
Could someone please tell me why the following section of code gives -317 days average dwell when there is only one part, (for info [Date Raised] is 24/11/2000 and [theMonth] is 01/11/200)? DwellAvg: CInt(DAvg("datediff('d',Dateserial(year([Date Raised]),month([Date Raised]),day([Date Raised])), #" & DateSerial(Year([theMonth]),Month(DateAdd('m',1,[theMonth])),1) & "#)", Could it be something to do with UK/US date formats and if so how can I remedy this? I know it is a problem in this part of the code as the query gives the correct count of e...

due date again
I was trying to find a correct formula that would give me due dates. eg: if column A contains a date or the word NA and column B contain the formula plus 12 months I would like column B to show either NA o the new date plus 12 months. The formula I used wa =DATE(YEAR(A1),MONTH(A1)+12,DAY(A1)) 1999-08-12 2000-08-12 NA #VALUE! Once that formula completes, it should notify me by the 10 month t send out a notice that it is due on or before the date in column B. Would the if function work here? I would appreciate any help anybod can give me. Thanks again -- Message posted from...

Symbol Updating Only Every Few Days (if at all)
Using Money 2006, and have a symbol "VLO" that is only updating every few days. This stock was a duplicate (downloaded transaction created a new version of the same stock - my fault not clicking correct choice when asked). I've removed the symbol from the stock entry that was downloaded, renamed this entry to something bogus, deleting this renamed stock "from all accounts", then added the symbol back to the original VLO stock that I've been tracking for years. Now the stock just says "unch" in the portfolio view, and the price history is only updat...

1092.00 $ and more from home during the following 30 days
Take part in our OPEN AIR TRAVEL and learn you How one extra 1092.00 $ and more from home during the following 30 days Without having to win over employee...Show here http://Olaf.ProWealthResults.com http://www.ProWealthSuccess.com/?SITE=98341 Congratulations 16727 Pre-Enrollees and 1462 Members have already signed up since you became a Pre-enrollee...and it is STILL GROWING! Remember, these people that you see below you in the Powerline are JUST LIKE YOU! They are looking for a powerful and simple way to make money from home without having to spend a lot of time or money to do it. Becau...

Auto moving by Date in Outlook 2002
Does anyone know if you can tell Outlook to move emails older than 90 days to a folder you specify in Outlook and then in another 30 days delete the emails in that folder? I'm running Exchange Server 2000 on the server and Outlook 2002 on the client pc's. I can't find anyway in Outlook to do this using the rules wizard or Auto Archive. I don't think there are setting to tell Exchange to do this instead of Outlook. But I'm sure there has to be a way. Any ideas? Much Appreciated. Lisa ...

excel corresponding a formula with a date
I have multiple formulas running down my page in column b to rip certain things from my sales reports that i have saved in my documents. When i save these sales reports they get saved with a certain date of the week ending.I need a formula that will be able to correspond with the dates going down the page in column a, as i dont want to go down through hundreds of formulas to manually change the date. there must be a certain formula that can be included in my formula to rip this certain information. 06/12/09 ='Macintosh HD:Users:Liane:Documents:[DeeWhySalesreport061209.xls...

Sent Mail Dates Replaced with word NONE
I am using Outlook 97 on a W2k machine. I have suddenly developed a problem in the Sent Items folder. Several weeks back the sent date changed from the usual date to the word NONE. The other folders seem to be OK. What and where can I check settings to look for the root cause of this? TIA, Carl -- Wishing you a VERY HAPPY NEW YEAR!! May it far exceed your expectations. ...

Can I make my heading bigger to include a pic?
I have a logo in my heading but it's bigger than the heading. If I could make the heading bigger to include this area, I wouldn't have text on the log while typing the document. You haven't said which version of Word you are using, but this is for 2007. Do you mean in your "Header"? If that is the case you can select the picture and click on "text wrapping" and select "send to back". Then close the header. You can also go to "Page Layout" and change the size of the header there. "KC" wrote: > I have a logo...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

Exchange serbice pack 1 it include du4?
Hi, I want to know if Exchange 2003 serbice pack 1 does include "Debice update 4" or not? Is it required as to be separate download or is it include in sp1? Thanks to everyone You can read list of features and details update by download the Release note at http://www.microsoft.com/downloads/details.aspx?familyid=A49560AD-4C8B-4449-9947-B054D7F12CCE&displaylang=en Jay "Steven Thorensen" <stthorensen@deleteherehotmail.com> wrote in message news:euiToXqfEHA.596@TK2MSFTNGP11.phx.gbl... > Hi, I want to know if Exchange 2003 serbice pack 1 does include "...

dates #5
Is there a formula to ad a numeric value to the name of a month. I am running a query in excel (pulling from an external source) and would like to sort as teh table refreshes by month and year. I have code to to do the refresh and the sort, but it sorts in alphabetical order. I was thinking a formula could place a numeric value to the month (1 = Jan, 2 = Feb, etc) and I can sort based on that. Is there an easy formula to do this. Thanks in advance Your easy formula could be: =MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun"...

Date Format #24
How can I get Excel (2003) to display a date as YYYYMMDD with no hyphens, periods or dashes between the YYYY the MM and the DD? I still need the cell to be recognized as a date. Thanks FORMAT / CELLS / NUMBER / CUSTOM yyyymmdd Hope this helped, Gary Brown "Adam1 Chicago" wrote: > How can I get Excel (2003) to display a date as YYYYMMDD with no hyphens, > periods or dashes between the YYYY the MM and the DD? > > I still need the cell to be recognized as a date. > > Thanks format=>Cells=>Number Tab, select custom and enter yyyymmdd -- Regards, Tom...

Format duplicate date in report
Hi Groupies How can I use conditional formatting to format a duplicate date in a report? I need the duplicates to show up but I would like them to be obvious. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "CJ" <private@newsgroups.com> wrote in message news:#eDXSWU#KHA.3880@TK2MSFTNGP04.phx.gbl... > How can I use conditional formatting to format a duplicate date in a > report? I need the duplicates to show up but I would like them to be > obviou...

Dates, time zones,daylight saving time
I live in the U.S. Eastern time zone (EST = GMT-5). An application I wrote his hosted on a server in the Pacific time zone (PST = GMT-8). I'm anticipating a time when I could have customers in yet other time zones, and each of them may have users that are themselves in multiple time zones. Even without going that far--even just serving my own users in my own time zone--I've had to make an adjustment. I've got a web page that shouldn't allow users to submit new data after a date and time that's stored in the database, April 15, 2010, at 10:00 PM. So I have Apr...

Delete email after x days
Is it possible to configure Exchange 2003 to delete email (or otherwise) automatically after it is, says 90 days old? thanks in advanced... Have a look at Recipients policies. You can create a Recipient policy for mailbox management. Serge "Phil" <Phil@discussions.microsoft.com> wrote in message news:73A24B9C-B434-4561-8EB5-815B4E356F5C@microsoft.com... > Is it possible to configure Exchange 2003 to delete email (or otherwise) > automatically after it is, says 90 days old? > > thanks in advanced... > On Wed, 9 Feb 2005 07:27:03 -0800, "Phil" &l...

Allow notes on SOP Trx line items to be included onEDD invoicing
It would be a huge benefit to clients if you could include SOP transaction line item notes to be pulled in the EDD invoice format. This would provide customers much greater flexibility and is, to our customer, a critical component to enable them to bill effectively. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and th...

Modified date
Please can anyone tell me how I can place in a cell on a sheet the date the workbook was last modified? Thanks Shane. Create a UDF '----------------------------------------------------------------- Function DocProps(prop As String) '----------------------------------------------------------------- Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function and enter in a cell such as =DocProps ("last author") or =DocProps ("last sa...

Getting the max date after filtering out the deleted ones
Hello i have a little problem. my tblServiceReports has 3 fields that i need to manipulate. the fields are ProductID, ServiceReportDate and SRDateDeleted i also have a ServiceReportID field as the key for the table. each product in my db can have one to many service reports. what i am trying to do is create a query that pulls the max date for the service report so that i can show the last time the product was serviced. there is a catch, cause i know you think that what i just asked how to do in the last paragraph was easy. i also need to filter out bad data. what? filter BAD data, w...

Include GL posting date column for MDA Smartlist
Currently the only date available in the Smartlist for Multi Dimensional Analysis is labeled as TRX Date. This date is the document date. I would like to be able to have a month worth of MDA data jibe with the G/L fiscal period data. This could be done if the GL posting date was a column available in the smart list. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sugg...

User's search results don't include last 2 weeks?
Using Exchange 2003 Server which we switched to recently, perhaps two weeks ago. When my Outlook 2003 SP2 user tries a search on a folder, the only results that he receives are from 2 weeks ago. You can manually open emails from the last 2 weeks that have the desired search string, and it doesn't matter what you search for, you can't get it to come up with any items in the last 2 weeks. I setup Outlook with his user account on another computer and get the same result. This must be an Exchange issue. Any ideas? I did do the migration from Exchange 5.5, and this old server is stil...

Need help converting date time to date only
I have a column in my spreadsheet with date and time, you can see in m attachment. I want to convert this column in to date only. Pleas help. Thanks Geng Attachment filename: sales - open orders test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=57210 -- Message posted from http://www.ExcelForum.com Hi without looking at your file you could use the following formula in a helper column =INT(A1) and format this cell as 'date' -- Regards Frank Kabel Frankfurt, Germany > I have a column in my spreadsheet with date and time, you can see in ...

Date problem #3
Hi All, I have a problem with the cell display if a dependant cell is empty. I have a date tracking spreadsheet. It tracks the date visits were made to clients. At the top, I have Week of (cell A2) to (cell C2) to display the week start and end dates. The week runs Mon to Sun. The first blank is cell A2 and the user must enter a date, the second blank, cell C2 contains =A2+6 to display the end of the week date. I then have 7 columns, one for each day of the week. Monday thru Sunday. I am trying to get the date to display below each day of the week based on the entry in cell A2. So I have enter...

Date Format #19
Can someone tell me how to change the format of the header/format date? Currently its is printing out as 24-11-04 , would like it to be Nov 24, 2004 I went to the global settings and chose the format that I prefer but it still will not change. Thank you. It doesn't look as if Excel will write out month names even when such names are specified under Control Panel as part of your short date format. MS suggests using a macro to set the footer: http://support.microsoft.com/default.aspx?scid=kb;en-us;161607 -- Jim Rech Excel MVP "Dawn" <dawn32@yahoo.com> wrote in m...