I can calculate the networkdays between two dates but how do I project a finishing date if I know the starting date, the holidays, and the duration of the task in working days? (Sample sheet attached, working in Excel 2003).
View 2 RepliesI would like to be able to calculate the number of Net Working Days between 2 dates using the dates returned from the calendar form and have the number of working days placed into a separate cell. Example, I use the popup calendar in A1 and select 10/1/06, I then use the popup calendar in B1 and select 10/9/06 and the number of Net Wroking Days is placed in cell C1. Is there a way to calculate the number of working days between the two chosen dates?
I have the forms for the calendar already created and am using this for the popup calendar but don't know where to start to calculate the working days from the calendars results.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This will launch when A1 and/or B1 is selected
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
CalendarFrm.Show
End If
End Sub
I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0
1
2
3
5
7
10
15
20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
I have employees that have different number of business days they work. I
need to be able to calculate when the employee has utilized a specific number
of business days specific to the days of the week they work and the number of
days per week work. For example, if I have an employee that works 3 business
days per week (Specifically M, W, and F), and I need to know the date this
employee worked a total of 30 business days, is there a way to calcuate this
date (which should be 6/9/06 if we use a start date of 4/3/06.
I have 3 columns of dates (A1, B1, C1).
Several different scenarios:
1. If A1 is blank, but B1 & C1 are populated, I want to put the result in
D1.
2. If B1 is blank, but A1 & C1 are populated, I want to put the result in
D1.
3. If C1 is blank, but A1 & B1 are populated, I want to put the result in
D1.
4. If A1 & B1 are blank, I want to put "N/A" in D1.
5. If A1 & C1 are blank, I want to put "N/A" in D1.
6. If B1 & C1 are blank, I want to put "N/A" in D1.
Why are there 3 date fields you ask, the powers that be want it that way,
Hmmmm. Looks like I ruined my original posting while trying to mark it solved.
View 14 Replies View RelatedI have a job tracker program that daddylonglegs helped me with a few days ago. I thought I would be able figure this out on my own but failed. I've attached the file to help show what I need. I know the final ship date of a project. Sometimes my projects need to go out for teflon coating.
I need Networkdays to give me a TO TEFLON date that is 5 days before the final ship date and factor in weekends and holidays.
I have a spreadsheet that captures task start and end dates similar to project. It currently calculates Networkdays correctly, so if you have:
1. 1/1/06 - 1/31/06 (22 Days)
2. 1/1/06 - 12/1/05 (- 22 Days - showing dates have been entered incorrectly.)
3. 1/1/06 - No End Date (-27655)
=IF(ISERROR(NETWORKDAYS(E23,F23)),"NA",NETWORKDAYS(E23,F23))
My concern is with #3, is there a way to instruct the formula that if columna and columb are not filled in,the result should be blank. Previously I had it where it indicated NA - but the problem with this is - while it appears fine in Excel, when I pull it into Access to report on I get the -27655. This is throwing my reporting off.
I am trying to find a formula that will return the number of week days between two dates. My specific situation is that my job sets up work orders (WO) to be completed by our staff. We have 3 dates - the date the WO was created, the date the WO is due to be completed, and the date the WO was actually completed.
I would like to subtract the Complete date from the Due date. Generally, this should always equal zero because our staff should be completing WOs on the due date! But obviously that doesn't always happen. There are times that they complete them late, and times they complete them early (yay!).
The problem with NETWORKDAYS is that even when they are completed on time, the result is 1. This formula counts instead of subtracts. I adjusted the formula to =NETWORKDAYS(A3,A4)-1 which works fine for those WOs completed on time or completed late. But for those completed early, it adds (or subtracts, really) 2 days. So for a WO completed a day early, instead of it showing -1, it shows -3. I've attached an example of WOs and the NETWORKDAYS formula I've used so you can see.
Subtract Days.xls
I'm really looking for something that will subtract week days, not count them.
Is there a way to calculate an age based on Date of birth and current date?
View 3 Replies View RelatedI need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM.
Here is what I have:
A1 = 6/4/08 1406Z
B1 = 6/5/08 0402Z
For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.
I would like to calculate the total hours between two dates using a unique formula and without inserting new columns. I have attached an example of the data I'm working with.
View 4 Replies View RelatedIn my excel I want to use a calender date such as 05/21/09 and automatically add or subtract a specific number of days. For example: 10 days before 05/21/09....
I dont want to have to count back 10 days and manually enter it. Is there a way to have excel calculate and give me the date in my spreadsheet? i.e. automatically calculate (05/12/09)-(10 days)= 5/11/09. its not too hard when its 10 days, but when its -97 days, it can be very annoying.
I have 2 columsn with data that looks like this:
Creation DateResolved DateDifference in days7/1/2011 4:221/9/2012 14:587/1/2011 8:567/1/2011 13:517/1/2011 11:438/3/2011 17:157/1/2011 11:597/14/2011 14:077/3/2011 22:477/5/2011 11:037/4/2011 1:399/15/2011 15:207/4/2011 8:367/5/2011 9:357/5/2011 7:047/6/2011 9:107/5/2011 9:177/6/2011 10:007/5/2011 10:471/9/2012 14:37
I am trying to figure out what formula to use to calculate the difference between the creation date and resolved date in hours and days. That is, In once column I would like to see the difference in 'hours' and in another column I would like to see the difference in 'days'.
Calculate dates as The Number Of Days, Months Or Years Between Two Dates
The two formulas I tried below show the wrong output when trying to figure the days between 12/8/2011 to 1/2/2012. It should show 0 years, 0 months, 24 days, not 138 days.
I did notice when the day of the month on the start date is before the day of the month of the end date, the formula seems to work. Ref. the second dates on the first example.
Example 1
Start End
12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Wrong display. should show 0 years, 0 months, 24 days
Start End
12/1/2011 1/2/2012 = 0 years, 1 months, 1 days
Note that when the start date "day", (1), is before the end date "day", (2) the formula seems to work.
First formula used above:
=DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"y")&" years, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"ym")&" months, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"md")&" days"
Example 2
Start End
12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Should show 0 years, 0 months, 24 days
Second formula tried above:
=DATEDIF(B10,C10,"y") & " years, " & DATEDIF(B10,C10,"ym") & " months, " & DATEDIF(B10,C10,"md") & " days"
I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.
What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.
I guess we can try to do this one step at a time.
The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?
IE:
Ticket Opened ...................Ticket Closed
5/16/2008 2:54 PM..............5/19/2008 10:47 AM
Total Time in Days / Hours / Minutes
Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.
This is more of a query on date dif
I was trying to calculate the difference between two dates on excel
I had a look through the search message board and searched the web generally where I came across the datedif function
I had found that in order to get the number of years it was
=datedif(startdate,enddate,"Y")
for months same formula"YM" and for days use "MD" in place of "Y" in the original formula
My question is when using datedif to calculate the number of days between two days it initialy gave me the right answer
Number of days between 01/08/2008 - 22/08/2008 - Answer 21 days
Using date dif gave me 21 days but when i did
01/08/2008 - 01/09/2008 - this gave me 0
Am I entering the formula wrong or does datedif simply not pick the difference if the days havent changed even though the month has ?
I then found i could simply minus one from the other , which then had me asking what is the point of date dif
I am trying to map the total networkdays across the months of the year for a list of contracts with variable start and end dates.
Example of the results I'm after:
Contract StartDate EndDate January February March etc.
Contract1 15 Jan 07 13 Jul 07 13 20 20
Contract2 5 Feb 07 2 Mar 07 0 18 2
I assume that the add-in NETWORKDAYS function is involved (and I'm ordinarily comfortable with using this function with only two dates and my named range of holiday dates), but I can't seem to figure out how to use it in this more complicated way. My own extensive struggles with it have led to ridiculously long and nested formulas that even I didn't understand after a few days away from them. I've searched the posts and seen questions similar to mine, but not close enough to give me a solution.
I thought this would be simple but I can't figure it out. I need to subtract the time between two cells. For example:
Cell A1: 6/6/2007 3:00:00 PM
Cell A2: 6/7/2007 2:00:00 PM
Result would be either 0:23 or 23 as in hours.
I am building a template to auto populate budget value by straight line based on task start/end date criteria. some tasks could give me correct duration distribution, but some don't. I am looking for If function, which could give me correct monthly allocation budget. Here is an example of what I am trying to achieve. task 0110 duration is from 09/15/07 to 05/24/08, duration month is 8. budget $34,465supposed to be allocated to 8 month starting from Sept, 07. but my if logic return 9 month instead.
View 8 Replies View RelatedI have a countif formula that calculates the number of entries between 2 dates. However i now need to also count how many of these match a 3rd value.
So i will need to work out how many "are greater than or equal to B2" "less than or equal to C2" and "equalling D2"
what i have already
=(-COUNT('Current Work'!AE8:AE2000)+COUNTIF('Current Work'!AE8:AE2000,">="&B2)+COUNTIF('Current Work'!AE8:AE2000,"<="&C2))
is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps?
For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15
Example.xlsx
how to calculate the difference in value of an item between two dates on a pivot table. I have one table that feeds the Pivot Table and it has three fields:
Data as of Date
Item
Value
So each day, the Data as of Date is updated to the current day and the items are the same but the values are different. I need to calculate the difference in value between the most recent date and x days ago. I cannot figure out how to do this via Pivot Table.
I am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
View 2 Replies View RelatedI'm just trying to calculate the difference between dates in two columns and applying conditional formatting based on if it is a positive or negative number. But when there is a null value in either of the date columns I don't want anything returned.
So far I'm either getting a 0 or a FALSE.
Column A = Date 1 Column B = Date 2 Column C = Formula
=IF(NOT(ISBLANK(B2)), B2-A2, )
I need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View 4 Replies View RelatedIf I have the start date (say February 01, 2009) and the job will take 100 working days, when will it finish if there is no work done over weekends? I assume you can use networkdays somehow but the final logic escapes me
View 2 Replies View RelatedI have a calendar in the sheet attached. If there are leave dates that are marked in red, can I create a formula to count the number of leave dates for the entire year ?
View 5 Replies View RelatedI have a spreadsheet that lists items in column A with a start date in column B. What I need to do is in in column C list the next review date in increments of 5 days, but I only want the next day to show from the current date, not the inputted start date.
For example:
Column A Column B Column C
Item Start Date Next Review Date
Item A 12-Sept-09 27-Sep-09
Can I get a formula to calculate the increments and only show the next calendar date for the review, and then to change to the next date once the previous date has passed.
Alternatively, I have another sheet in my workbook that calculates the dates, is there some way I can display the next future date in a line of dates?
I'm not too hot with Excel formulas but I'll explain as best I can. I have 3 columns with dates and times
A - 01/08/2008 08:17:08
B - 01/08/2008 12:17:08
C - 01/08/2008 17:00:11
This is what I want to do...
If A > B then Calculate the time difference between A and C
If A <= B then Calculate time difference from B to C
I'd like to get this formula in 1 cell if possible?