Determine A Specific Date Based On A Number Of Business Days?

Feb 11, 2010

I'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.

ADVERTISEMENT

Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range

Jul 31, 2009

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

I'm using Excel 2007.

View 9 Replies View Related

How To Find Business Day Specific Number Of Days From Date

Oct 8, 2013

I have a spreadsheet that contains dates in format mm/dd/yyyy. Lets call this Column C(Paydate). In Column A I have a date that needs to be a 2 business days before the paydate. If this date in column A winds up being a Saturday or Sunday, the function needs to return the previous Friday. If the date is any weekday then it should return that weekday

For example:
C1 = 10/18/2013(a Friday) then A1=10/16/2013
C2 = 10/14/2013(a Monday) then A2= 10/11/2013(the previous Friday since 2 days before 10/14/2013 is a Saturday)
C3 = 10/15/2013(a Tuesday) then A3= 10/11/2013(the previous Friday since 2 days before 10/15/2013 is a Sunday)

View 4 Replies View Related

Userform VBA Which Will Determine Business Days

Feb 18, 2009

I have created a useform in VB which will determine business days. I need help in the coding part.

In my user form I have text box, I have a calender and I have two command buttons, 1 Calculate 2 exit. What i would like to achieve is when i type a number in the txtbox and press cmd button calculate the code will shade the the date in the calender so for example if I type in 10, then the code should shade 13/02/09 on the calender since 13/02/09 is the 10th business day.

View 9 Replies View Related

Calculate End Date Using Start Date And Number Of Days Excluding Specific Days

Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:
*LU = lesson units; 1 LU is 45 minutes

Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14

[Code] ...........

View 7 Replies View Related

Determine Days Based On Due Date

Sep 1, 2007

I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...

I cannot figure out a formula or a way to determine:
If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..

View 4 Replies View Related

Adding 1 To A Number On Business Days

Sep 16, 2006

I'm working on a complex A/R aging summary. So Ill have couple of question, but right now I'm trying to have excel automatically keep track of the A/R process. For instance every business day I would like the number of days the Invoice is outstanding to go up by 1. So when I get to work on monday the invoices that have been due for 20 days will now show 21 without me touching it.

View 5 Replies View Related

Calculating The Number Of Business Days In A Specified Period

Dec 31, 1969

It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g

Mondays, Wednesdays and Fridays of June
June 1, 2006
June 3, 2006
June 6, 2006

Tuesdays, Thursdays and Saturdays of June
June 2, 2006
June 5, 2006
June 7, 2006

This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months

View 14 Replies View Related

Calculating Number Of Business Days Between Two Dates?

Dec 31, 2013

Calculating Number of Business Days Between Two Dates?

View 9 Replies View Related

Adding Business Days To A Date Formula

Dec 8, 2008

I am looking for a formula that looks at a date and could add business days to it.

for example:

If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08

View 6 Replies View Related

Macro To Add Business Hours / Days To Date

Dec 24, 2012

Where i want add Business Hours/Days to get the Target Date.

Working hours: 8:00 AM to 19:00 PM

Urgency Type:

ND (Maximum 1 Day 11 hours)
3D (Maximum 3 Day 11 hours)
4D (Maximum 4 Day 11 hours)
5D (Maximum 5 Day 11 hours)
Threshhold - 16:30 PM
Weekends to be Excluded.

Holidays to be Excluded based on Country. Say I have 2 countries India and America, where America has holiday on 12/24/2012 but India does not have holiday on that Day. Hence while calculating Tgt date, India should be calculated normally, for America holiday date need to excluded(There will be a Holiday table based on the countries, not limited to 2 countries).

ex: 1) A ticket opened at 12/26/2012 8:56 AM; Target date will be 12/26/2012 19:00, in this case its 1 day 10:04 hours added to the Opened Time.

2) A ticket opened at 12/26/2012 16:56 PM; Target date will be 12/29/2012 19:00, in this case ticket opened after threshold time of 16:30, Technically ticket should start from next day i.e 12/28/2012 8:00 AM and 1 day 11 hours added to this date.

3) A ticket opened at 12/26/2012 7:56 AM(Before the begin of Working hours); Target date will be 12/26/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time.

4) A ticket opened at 12/21/2012 17:56 PM(After Threshold time); Target date will be 12/24/2012 19:00, in this case its 1 day 11:00 hours added to the Opened Time. If the above ticket belonged to America, then holiday dates(both 24 & 25) need to be skipped and Tgt date will be 12/27/2012 19:00.

Here is the Sample Data

Country
Ticket
Start Date/ Time
Tgt Date/Time
Expected Tgt Date/ Time

America
ID000123
12/21/2012 18:20

[code]....

I need results to be appear in the "Tgt Date/Time" column. I have also provided results in "Expected Tgt Date/ Time".

View 1 Replies View Related

Moving Columns Automatically When Date Changes Business Days Only

Aug 7, 2012

I would like to move columns to the right as the date changes. For example, the top of the column has the current date. Within the column are about 40 rows of data. as the date changes, I would like that column to automatically move to the right and show that column with yesterday's date, and have that data associated along with it moved. I am looking for 6 months of columns to store, with the current date in the first column and subsequently moving right with the previous dates and info.

------------------------------------------------------------------------------------------------
August 7, 2012 | August 6, 2012 | August 5, 2012 | August 4, 2012 | August 3, 2012
------------------------------------------------------------------------------------------------
Accepted Processed Accepted
Rejected Accepted
Processed Processed
...
and so on

View 2 Replies View Related

Number Of Days To Add To Date Based On Weekday Of Date

Apr 23, 2008

I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008

View 4 Replies View Related

Change Cell Colour Based On Number Of Days Before Date?

Jan 17, 2014

creating a cell to show: no colour unless
date minus 90 days as amber
date minus 45 days as red

View 6 Replies View Related

Counting The Number Of Days Overdue Based On Date And Time?

Jun 17, 2014

I have the following:

A1 = Date item is due. (DD/MM/YY format)

A2 = Time the item was delivered (HH:MM:SS format)

B1 = Actual Date item was delivered.

B2 = Actual Time item was delivered.

I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.

For example:

Due Date: 17/06/14 @ 16:00:00

Arrival Date: 17/06/14 @ 16:01:00

Days Late = 1

Due Date: 17/06/14 @ 16:00:00

Arrival Date: 18/06/14 @ 15:00:00

View 10 Replies View Related

Counting Number Of Days Overdue Based On Date And Time

Jun 17, 2014

I have the following:

A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)

B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.

I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.

For example:

Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1

Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1

Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2

View 3 Replies View Related

Excel 2007 :: Count Number Of Days Between Two Date Where Off Days Are Friday / Saturday And Holiday

Mar 14, 2014

I am making process TAT(Turn Around Time) which required following information. In Excel 2007.

1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays

2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays

Note : Any weekend (off days) dates listed in holidays should not effect the query.

View 9 Replies View Related

Determine Row Number Of A Specific Value

May 13, 2007

I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.

View 9 Replies View Related

Count How Many Days Coming Due Within 90 Days Of Each Date Based On Today Function

May 27, 2012

There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.

Countif Today()+90

View 5 Replies View Related

Determine Date Of Nth Specific Day Of Month

Apr 9, 2008

See attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.

View 7 Replies View Related

Tat Calculation Only For Business Days

Dec 4, 2008

I am not able to calculate TAT(Turn around Time) between two dates without taking Off days. Plz help me on this.. File is attatched as per example..

View 6 Replies View Related

Caluclating Business Days

Jun 30, 2006

I am looking to calculate business days - more specifically Monday through Friday. I am not currently worried about holidays or vacations, yet I wouldn't mind including it if I could have a list to "check" from.

I tried previous searches, but found a few functions that I don't have on my computer.

View 6 Replies View Related

Create Formula That Will Determine Number Of Orders For Specific Customer?

Aug 29, 2012

I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.

Here is what I was trying to use, but it was coming up with "0" for everything:

=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"

View 1 Replies View Related

Formula To Generate 3-5 Business Days

Mar 28, 2008

we have salespeople in all 50 states and each state uses different rules for business days. Some states include Saturday as a business day, some don't, and Alaska uses 5 business days including Saturdays. We are open 7 days a week.

What I am trying to create is a worksheet that has each day of the month going down and in the cells next to it, a column for states that include Saturdays as business days; so a date 3 business days out would appear in the next cell. In the cell after, a date 3 business days out not including Saturdays. And in the last cell for Alaska, a date 5 business days out including Saturday. Of course federal holidays are all excluded.

Something like this:

March w/ Sat w/o Sat Alaska
1 5 5 7
2 5 5 7
3 6 6 8
4 7 7 10
5 8 10 11

Now Vermont has extra holidays certain months so I would have to adjust that in the formula for the middle column during those months.

View 9 Replies View Related

Add Days To A Specific Date

Jul 11, 2007

A1=10 July 2007
A2=12 (number of days)
A3=21 July 2007

I need a formula in A3 that will basically add the number of days in A2 to the given date in A1.

Note: Don't know how would this work with 30 and/or 31 days months??

View 9 Replies View Related

VBA Less Than Specific Date But No Greater Than 28 Days

Jun 19, 2014

I'm using the code below to extract information from a 'Source' (Remaining Allocation Data) to a 'Destination' (All CTO Expired Assignments) sheet.

[Code] ......

The code works except for this part of the script

[Code] ....

What I'm trying to do is as follows:

Extract records where the FinDate is less than the CurrentDate, (i.e. 19/06/2014) and,

Is equal to and no greater than 28 days prior to the Current Date (i.e 22/05/14)

I've tried all sorts of formulas to try and get this to work including the 'DateAdd' function, but I've so far been unsuccessful.

View 2 Replies View Related

Add X Days To Date Excluding Specific Day

May 18, 2008

the formula below prints monday to friday when i drag it down. Probably a very easy question but how would i change it so when i drag it down it includes saturdays so the only date missed out is Sunday.

It picks the date from cell a4. the code is from here: http://www.cpearson.com/excel/DateTimeWS.htm

=IF(WEEKDAY(A4)=7,A4+2,IF(WEEKDAY(A4)=6,A4+3,A4+1))

View 4 Replies View Related

Determining Number Of Specific Days In Period

Jun 25, 2009

I am wondering if there is a way in XL2003 to determine how many times specific days of the month occur within a specified date range. For example, I need to find out how many times both the 1st of the month, and how many times the 15th of the month occur in the time period starting 05/02/2009, and ending 07/12/2009.

View 3 Replies View Related

How To Create Formula To Add 30 Days Out From Specific Date

Sep 15, 2009

I am creating a tracking spreadsheet and I will need to f/u on a process 30/60/90 days out from a particular date. Example: CPAP Therapy began 1/22/09 and I need to follow-up by 2/22/09. How do I create formula to add 30/60 or 90 days out from the setup date?

View 6 Replies View Related

Calculate Specific Days Within Specified Date Range?

Jan 20, 2013

I have a form with grouped check boxes for each day of the week, and start date and end date from the Microsoft Date and Time picker control. I want to know the total days within the date range based on check boxes. For example, Tuesday and Thursday are checked off. The start date is December 1, 2012 and end date is January 31, 2013. What is the total number of Tuesdays and Thursays in that two month date range.

View 2 Replies View Related

Automatically Highlight Fields With Dates Older Than 5 Business Days?

Dec 17, 2012

I have columns of dates and I'm looking to automatically highlight dates that are older than 5 business days from today. Is that conditional formatting?

View 3 Replies View Related

Difference Between Dates & Times In Business Days With Time Constraints

Aug 16, 2009

I need to create a spreadsheet that will calculate the difference between two dates and show the result in days. The complicated part is that weekends (Sat and Sun) need to be ignored and any time after 4:30 pm needs to be treated as the next business day. I have been trying to look at similar questions in forum and use the WORKDAYS function with little success.

Example:

A1- 14/08/09 4:40 p.m.
B1- 17/08/09 9:10 a.m.

I need the answer (B1-A1) to show up as 0. But if the time in A1 was changed to 4:20 p.m. than the answer should be 1 day.

View 5 Replies View Related

Calculating Number Of Days In Specific Month Given Range?

Aug 29, 2013

At the end of each month, my agency collects data about clients served in our programs. One of the many pieces of information they want is the number of "bed days" a client was residing within our programs for the given month.

For example:

Jon Doe entered the program on 7/16/13 and discharged on 8/15/13. I would like to know how to calculate the number of days in July that Mr. Doe was residing in the program. (The date of discharge is not included.)

I recently took over the responsibility of completing reports such as this one at my office. The previous person who completed the monthly reports would count the days by hand for each client, but I know there must be a better way.

View 6 Replies View Related

Date Countdown: Results Of The Number Of Days Between Today And A Future Date

Apr 8, 2009

I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?

View 3 Replies View Related

Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date

May 19, 2009

I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:

5/10/2009 to today is -9

5/22/2009 to today is 3

View 2 Replies View Related

Calculate Number Of Days Between Two Date Within A Current Month Including End Date

Apr 2, 2009

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

View 9 Replies View Related

Extract Character From Date Format & Number Of Days Between Date

Jan 17, 2010

I have two questions regarding date format and hope you can provide input.

1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?

2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?

View 2 Replies View Related

Calculate Number Of Days Between 2 Dates And Then Assign Number Based On Answer

Mar 22, 2014

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

OR

Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

View 9 Replies View Related

Formula For Date Minus Date = Number Of Days

Nov 15, 2008

I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.

Example: 04/31/08 - 04/01/08 = 30 (days)

View 2 Replies View Related

Stop Count Of Days When Date Is Entered On Specific Cell - Excel

Mar 17, 2014

excel.jpg

how to make my network days stop counting when I insert a value in the completed column (in this case H3). I have a Date Received column (A3), a due day column (G3) and the last column with the date (N3). My remaining days column keeps counting after I filled the completed cell (H3) and won't stop counting after. I need a formula to stop after the completed cell is fill up.

View 5 Replies View Related

Formula To Add Number Of Days To Date

Apr 29, 2014

I want a formula to add the number of days to the date

Such as 90 or 180 days for this date 01/01/2014

View 7 Replies View Related

ADVERTISEMENT