Calculate Due Date?

I need a formula that will calculate a due date 10 days from the start
date (a1)but if the date falls on a weekend or holiday I need the date
to be the next working day.

I will have a list of holidays in J2:J50.

Thanks for any help..........

0
ranmcc (14)
7/11/2005 5:02:06 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
424 Views

Similar Articles

[PageSpeed] 49


-- 
 HTH

Bob Phillips

"Randy" <ranmcc@msn.com> wrote in message
news:1121099149.930051.258040@o13g2000cwo.googlegroups.com...
> I need a formula that will calculate a due date 10 days from the start
> date (a1)but if the date falls on a weekend or holiday I need the date
> to be the next working day.
>
> I will have a list of holidays in J2:J50.
>
> Thanks for any help..........
>


0
phillips1 (803)
7/11/2005 6:09:41 PM
Don't you just want to use NETWORKDAYS, it handles weekends and holidays.

-- 
 HTH

Bob Phillips

"Randy" <ranmcc@msn.com> wrote in message
news:1121099149.930051.258040@o13g2000cwo.googlegroups.com...
> I need a formula that will calculate a due date 10 days from the start
> date (a1)but if the date falls on a weekend or holiday I need the date
> to be the next working day.
>
> I will have a list of holidays in J2:J50.
>
> Thanks for any help..........
>


0
phillips1 (803)
7/11/2005 6:10:10 PM
See my response to your first post. No need to multi-post.

-- 
HTH,
Bernie
MS Excel MVP


"Randy" <ranmcc@msn.com> wrote in message 
news:1121099149.930051.258040@o13g2000cwo.googlegroups.com...
>I need a formula that will calculate a due date 10 days from the start
> date (a1)but if the date falls on a weekend or holiday I need the date
> to be the next working day.
>
> I will have a list of holidays in J2:J50.
>
> Thanks for any help..........
> 


0
Bernie
7/11/2005 6:15:08 PM
Bob,

The OP only wants to change the date if the end date falls on a weekend, not if the 10 date period 
covers a weekend (which it always will).

HTH,
Bernie
MS Excel MVP


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message 
news:ehkaMQkhFHA.2472@TK2MSFTNGP15.phx.gbl...
> Don't you just want to use NETWORKDAYS, it handles weekends and holidays.
>
> -- 
> HTH
>
> Bob Phillips
>
> "Randy" <ranmcc@msn.com> wrote in message
> news:1121099149.930051.258040@o13g2000cwo.googlegroups.com...
>> I need a formula that will calculate a due date 10 days from the start
>> date (a1)but if the date falls on a weekend or holiday I need the date
>> to be the next working day.
>>
>> I will have a list of holidays in J2:J50.
>>
>> Thanks for any help..........
>>
>
> 


0
Bernie
7/11/2005 6:22:12 PM
Ah, me might say that bus does he mean it. I struggle to think of a reason
to have that algorithm, so I make a suggestion based upon a more likely
(IMO) premise. He can take notice or ignore, it's his choice.

Bob

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:O1VFRVkhFHA.3436@tk2msftngp13.phx.gbl...
> Bob,
>
> The OP only wants to change the date if the end date falls on a weekend,
not if the 10 date period
> covers a weekend (which it always will).
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:ehkaMQkhFHA.2472@TK2MSFTNGP15.phx.gbl...
> > Don't you just want to use NETWORKDAYS, it handles weekends and
holidays.
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > "Randy" <ranmcc@msn.com> wrote in message
> > news:1121099149.930051.258040@o13g2000cwo.googlegroups.com...
> >> I need a formula that will calculate a due date 10 days from the start
> >> date (a1)but if the date falls on a weekend or holiday I need the date
> >> to be the next working day.
> >>
> >> I will have a list of holidays in J2:J50.
> >>
> >> Thanks for any help..........
> >>
> >
> >
>
>


0
phillips1 (803)
7/11/2005 9:10:41 PM
Reply:

Similar Artilces:

how to print email without print out today's date?
All I need is to printout the emails from outlook express 6 without printing out today's date that shows up at the right bottom corner of each page. I tried using Advanced button in the print window, change printer properties and all other ways I can think but could not find the solution. I use windows XP. please help me. Do you have a new version of Microsoft Office installed on your computer? If so, see if you have the "Microsoft Office Image Writer" printer installed in your Printer applet in Control Panel. If so, use that printer to save your message and print it or vie...

Date Value
G'Day, The date in my excel sheet is formatted to the serial number, I cannot get it back to display the date in year/month/day format. I am not too sure why and I have looked for answers in the help menu with no luck. Thanks For the Help Hi have you tried 'Format - Cells - Date'? -- Regards Frank Kabel Frankfurt, Germany "Blinky" <Blinky@discussions.microsoft.com> schrieb im Newsbeitrag news:97948CD1-80DB-4F94-9F34-BB8BE3454670@microsoft.com... > G'Day, > > The date in my excel sheet is formatted to the serial number, I cannot get > it back ...

Auto-Calculate
I have a spreadsheet that utilize the stock quote add-in from MSN. On a daily basis I refresh the add-in functions for the Highs, Lows, Last, Volume, etc. On occasion the spreadsheet reverts to manual caculation settings, and I have to press the F9 even after I updated the quotes. I've always preferred the any spreadsheets I open, blank or otherwise, the automatic setting selected for calculations. I consider this an annoyance. Has anyone experienced this glitch? Is there a fix for it? Ron_D Ron Excel takes the calculation mode from the first workbook that opens in a session. Sub...

Difference in dates
Using Excel 2007, I would like to see the difference between 2 dates. 1 column is formatted as: month/yr. I would like to use the (now or today) command in the second column. When I open up the spreadsheet, I want the difference between the 2 columns to reflect today's date. Thanks Hi Charles, Assuming the first date is written in Cell A1, then Write inside cell B1 the following formula: =Today()-A1 if the formula's result is showing as a date, then format that cell as a number. ------ http://www.free-training-tutorial.com "Charles Eaves" wrote: > Using E...

Smart List Export to Excel
When I export smart list data to an Excel Spreadsheet, I cannot use the month function on date fields. They look like dates, and have serial numbers like dates, but the function returns 01-04-1900 instead of the month. Is this an Excel issue or an issue with date fromatting from samrt lists? thanks -- Bob G Bob, What versions of GP and Excel? I just tried it on my GP 9.0 SP 2 and Excel 2007 - dumped a bunch of Account Transaction lines to Excel, and the MONTH function worked correctly...... -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Bob Gunderson" &l...

date/time formatting
I'm trying to duplicate an Excel date/time format in Reporting Services 2005. On the Excel sheet, the difference between two dates is displayed formatted as "150:58:12" and then as "6.29". (12/1/2009 9:41:49 AM - 11/25/2009 2:43:37 AM). Is there a way to format the date/time in Reporting Services to do this? dataGirl You can format dates with .NET formatting yyyMMdd os something like that "dataGirl" <dataGirl@discussions.microsoft.com> wrote in message news:747727D3-CA46-49BE-ACED-F7D5418FE7BF@microsoft.com... > I'm tryin...

Changing Default Date format in excel
Hi I want excel to dislay date in the format ( dd-mmm-yy) ( like 21-May-04 ) not only for viewing but also in the FORMULA bar Its because I am recording a macro which will copy today's date and save the file with name of the file having today's date in dd-mm-yy format, so that confusion on whether 01-02-04 stands for Jan 2nd 2005 0r Feb 1st 2005 may not be ther I used today( ) function and then copied today's date and did paste special in to a blank new cel Then in the new cell I changed the date to the format dd-mm-yy but the formula bar stil displays the date in the format mm/d...

how do i print a list of names and dates in excel into a two-colu.
i have a list of names and dates comprising three columes in excel. How do i print this list in an array two colums wide on 81/2x11 paper. i.e.: NAME....DATE....DATE NAME...DATE....DATE there is enough data to fill four pages. data needs to be sorted so that new names can be added alphabetically. in page setup on the sheet tab, try selecting "over, then down" as the page order. "Captainbob" wrote: > i have a list of names and dates comprising three columes in excel. How do i > print this list in an array two colums wide on 81/2x11 paper. i.e.: > ...

Add a new column AS date using a prompt or form
I have a monthly sales report. I would like to keep track of the 52 wk sales dollars in a column field with the period end date AS the column header. Adding a new column at the end of every period. UPC 07/14/07 08/11/07 000834663049 $19,812.20 $19,812.20 001115211655 $5,323.91 $5,323.91 001117101118 $1,810.80 $1,810.80 I’ve tried to prompt a date & enter with a form (see SQL), with no luck. SQL: SELECT [ACN_TEXT].UPC, [ACN_TEXT]![52_week_doll] AS [Forms![frmDate]![txtStartDate]] FROM [AC NIELSEN TEXT]; -- Thanks, Kevin Take a look at crosstab queries. ...

Queries calculation
I have a table called “openbills”. I have a query called “billsopenvalue” In the table I have a field where I insert the amount a customer paid – this can be as well a part payment of the bill. The query calculates how much is still pending to pay. Now the problem: Ie: the total to be paid amount is 1000. The customer paid 100. = Result of query 900 Now the customer paid another 100 – normally the result of the query should be 800; but it is again 900 because the resting value is calculated: total to be paid – paid amount = resting amount. How can I fix after each calculation the restin...

convert date to week, starting at financial year
I have query that counts how many bookings were made on a certain date, but I need to total it by each week (for example 5 on monday and 2 on wednesday, week 1 total = 7) I have worked out that you use: Format([dteDate],"ww") for the week, but I need to set the earliest date selected as week one, as this would be the start of the financial year (earliest date is April first) how would I set this up? would I need a table with all the dates in with the week number next to it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/2007...

Calculate someone's age
I have a list of birthdays 11/17/1975 on cell A1 and 11/17/2010 on cell B1... on C1 I have this formula: =INT(YEARFRAC(TODAY(),A,1,1)) which makes the age 34 (if i format the cell to General or number) ...It tells me what age this person is but if I change the date on B1 to 11/17/2011 (for example), it wont change the age on C1 to 35... What is the formula to do this change? Thanks, Trish -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/outlook-contacts/201003/1 "LATINCUTIEPIE00 via OfficeKB.com" <u53174@uwe> wrote in messag...

Determine date based on day of month (i.e. 2nd Tuesday of the month)
I've used Excell for years and I'm usually pretty good at finding answers to my questions. However, this one has eluded me. I figure the answer is simple and I'll have a "DOH!" moment when it's pointed out... I'm trying to determine the date of the 2nd Tuesday of every month in 2007. Ideally, I'd have two columns. First would have the month, the second would have the specific date of the 2nd Tuesday for that month. Can someone kindly point me in the right direction? I can't seem to figure this one out. http://www.cpearson.com/excel/DateTimeWS.htm#...

Doing Date Comparisons
I have specific ranges of dates, such as between 1/1 and 1/10 of a given year, and I want to know if a date entered falls between those dates. However, as I mentioned, I have a range of dates so I need to scan all of the dates in the range before I get a true or false. Any ideas? Hi does your range of dates consist of 'real' Excel dates. Your example of '1/1' seems to idnicate either a custom format or a text value? -- Regards Frank Kabel Frankfurt, Germany Jason wrote: > I have specific ranges of dates, such as between 1/1 and 1/10 of a > given year, and I want to ...

Change Date Format
How do I change the date format of yyyymmdd so a query will identify weekend dates as a criteria? Thanks. If your field is a true DateTime then use --- Format([YourDateField],"w") with criteria of 7 or 1 for Saturday and Sunday. -- KARL DEWEY Build a little - Test a little "CMM" wrote: > How do I change the date format of yyyymmdd so a query will identify weekend > dates as a criteria? Thanks. Could you provide a little more description? How does a "format" have anything to do with "weekend dates"? Criteria for what? More info, pl...

transforming a date
I've a column with a date written as follows 20060801. (first of august 2006) I want to transform this date into 01-08-2006 Does anyone know how to do this? Bernd Select the column data|Text to columns Fixed width (but don't have any lines to separate fields) choose date ymd (or ydm, I can't tell!) and finish up. Format the column the way you want. bernd.vd.berg@gmail.com wrote: > > I've a column with a date written as follows 20060801. (first of august > 2006) > > I want to transform this date into 01-08-2006 > > Does anyone know how to do this?...

Date add Criteria on Continuous Form
All, thank you for all the previouis help. currently i have a form has three fileds to tracks letter "Recieve", "ResponseDue", and "Respond." on the recieve text box's afterupdate event i have:Me.ResponseDue = DateAdd("d", 30, Me.Recieve). what i need is that on the first letter, the minimum date, if an the main form the criteria is "NMA" that the date add be 60. thank you for the help in advance. AC2007/XP. Tighe - The easiest way is to Dim an integer variable and use that. Try this: Dim AddDays as Integer If Par...

Bar chart by division, department, region date
Hi. I'm very new to charting and hope you can steer me in the correct direction. I want to look at trends over a rolling 12 month, for a business unit that has many departments that each have regions. Dept 1: reg A (2 issues this month), reg B (3 issues this month), reg C (1 issue this month) Dept 2: reg A (3 issues this month), reg B (0 issues this month) Can this all be plotted in one bar graph? What would my spreadsheet need to look like and would a regular bar graph work? Stephanie, Thisis what I came up with: Cell A1 type "Dept 1", Cell C1 type "Dept 2" C...

Pivot calculations, is it possible this way?
Hi, I've a datadump from a database containing work hours from five different departments, categorized as several different types like 'sick leave', 'on training' etc and of course the usual workstuff categories... I'ld want a pivot table of a specifik work type per department, like 'on training', but I'ld like the value to be proportional to the amount of personal on that specific department. Is that possible, how? Any help would be appreciated! Hi Sebastian, I can only assume that you already have the pivot table there... on the top left of the ...

Duplicated date
Hi It's any function of queries to check then delete the duplicate date in one field? -rgds Mohsin -- Cheers Mohsin Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1 You cannot 'delete' a field, you can delete the WHOLE record, but not a single field in a record. You can SET a field to a NULL value, though. If there is 'a', one, duplicate row, there are TWO of them. Unless you have some means to differentiate them somehow, any SQL statement would affect BOTH rows. So, basically, use an UPDATE query that wi...

Date displayed in Wrong langauge
This is exactly what happened: I used MS Office X and everything has been alright One day all the date / time began to display in German I brought MS Office 2004 and installed it Same goes, all date / time display in German. My Mac has locale / language etc all set properly to English. Anybody has any idea? Thx Eno On 3/28/05 10:59 PM, in article 1112079571.332610.11520@z14g2000cwz.googlegroups.com, "eno tsin" <enotsin@hotmail.com> wrote: > > I used MS Office X and everything has been alright > One day all the date / time began to display in German > I brough...

Automatic Calculations of Text Material
I have a problem that I am unable to solve by reading the help file. I have text material in a column that contain "-", "?", "{", "*", "/", and so on (I formatted the column as text). Excel 2007 occasionally automatically tries to calculate the contents of this column and / or cells (for example, when I make some changes to the text in certain cells), which is unnecessary because as I wrote above the contents of the cells are pure text. How can I turn off these calculations for the entire sheet (or at least certain columns)? Yes, I know ...

Insert date automatically #2
I have a worksheet that has to be updated every working day of my company.How to insert today's date next to yesterday's date in date row,What function will insert the date automatically after 12 in midnight by considering that date is a working day or not for the company? I will try to answer your 2nd posting (a duplicate) of same question. "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> > will insert the date automatically after 12 in midnight by considering that ...

adding a date popup box
Hi, is it possible: instead of writing a date manualy in a cell, to open combo box (pop up) and choose the date ? can i do that in excel ? -- Message posted from http://www.ExcelForum.com Hi see: http://www.rondebruin.nl/calendar.htm -- Regards Frank Kabel Frankfurt, Germany > Hi, > > is it possible: instead of writing a date manualy in a cell, to open a > combo box (pop up) and choose the date ? can i do that in excel ?? > > > --- > Message posted from http://www.ExcelForum.com/ Frank, I've used your website in the past & it is extremely helpful. Ho...

Changing Time format in Date column
Hi, When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm I would like it be shown as 05/03/2010 16:35. Any help from anyone would be greatfully appreciated. Thanks, Jon. "Jon" <Jon@discussions.microsoft.com> wrote in message news:54D681D7-2367-434B-AC7A-29AD80A4A828@microsoft.com... > When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm...