Solved

Date Calculation in Excel 2007

Posted on 2011-03-20
11
565 Views
Last Modified: 2012-05-11
I'm stuck on a date calculation in excel.....I need the followin:  I have a start date of 3/21/2011 for every part of a project.  I would like to calculate the start day and end day based upon the Start Date and End date...to clarify, here are two examples:

Task number 1 starts on 3/21/ 2011 and ends on 3/24/2011....so the person would enter those two dates and automaticall have the following calculated.

Start Date      End Date     Start on day?              End on Day
A2                    B2              C2                               D2
3/21/2011      3/24/2011     1                               4

In addition, let's say they wish to start on 3/25 and not complete until 3/31:

Start Date      End Date     Start on day?              End on Day
A2                    B2              C2                               D2
3/25/2011      3/31/2011     5                               10

So basically, no matter when they start, it will show the actual DAY number that they started and the actual DAY number they finished as well as the dates.  That way I can create a GANT chart to track project status.

I hope I have given a good enough explanation, if not, just ask a question and I will respond!
thanks,
dale





0
Comment
Question by:dgore1
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35177103
Should end of day be 11 for the second task?

How about this one?

Curt
TimeCalc.xlsx
0
 
LVL 8

Expert Comment

by:wchh
ID: 35177346
Column C: =WEEKDAY(A2)-1
Column D: =SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Sunday"))+C1-1
Assume Column D only Exclude Sunday
0
 
LVL 8

Expert Comment

by:wchh
ID: 35177841
Column C: =1+A2-DATE(2011,3,21) (follow Curt)
Column D: =SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Sunday"))+C1-1
0
 
LVL 8

Accepted Solution

by:
wchh earned 250 total points
ID: 35177853
Please ignore previous 2 messages...

Column C:==SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE("2011","03","21")&":"&A2)),"dddd")<>"Sunday"))
Column D: =SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Sunday"))+C1-1
Assume calculations Exclude Sunday
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35178224
Hi Dale,

"I hope I have given a good enough explanation, if not, just ask a question and I will respond!"

I think we may need that response soon!

Maybe this is what you want. I'm now guessing normal weekends are time off the project. My first file had no days off since you didn't specify any days off.

Using this method you can also add a third parameter which will check a range for holiday dates to exclude.

Cheers,
Curt
TimeCalc-2.xlsx
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Assisted Solution

by:Curt Lindstrom
Curt Lindstrom earned 250 total points
ID: 35178259
To make it easier to handle you can make it as a table. The advantage is that you can just drag the right bottom corner of the table downwards to expand your table and the formulas are automatically added to all new rows. I also added an IF to not display the data from the formulas when there are no date in column A.

Curt
TimeCalc-3.xlsx
0
 

Author Comment

by:dgore1
ID: 35179286
Sorry....took some medicine for a cold and fell asleep!!!  I will check all solutions now!!  Sorry about that!
0
 

Author Comment

by:dgore1
ID: 35179364
Both of these work perfectly!!!!!!!!!!!  the formula from wchh skips Sundays which is great....and Saturday isn't that big of an issue...but if you have the formula for skipping the entire weekend, that would be great to add as well...just in case!!!

On the spreadsheets from epaclm, if you wouldn't mind, could you show the part about how to skip holidays?

At this point in time, both work so well, I would like to split the points between the two answers since they were quick and excellent!!

Also, what does this statement actually do?

=IF(A4="","",NETWORKDAYS($A$2,$A4))

If column A4 = null? and why twice?  I have never used the networkdays...but read up on that command...which automatically skips the weekends...pretty slick....
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35179734
The formula

=IF(A4="","",NETWORKDAYS($A$2,$A4))

1. Checks if there is anything in A4. If A4 is empty nothing will be displayed.
2. The formula will be displayed if A4 is populated and the date in A2 is the start date and A4 the end date.
3. any weekend days between the 2 dates will not be counted

I'll do the holidays a bit later.

Curt
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35179755
Not for Points

If you make a list of holidays then NETWORKDAYS can skip those too, e.g. list holidays in Z1:Z10 and change Curt's formula to

=IF(A4="","",NETWORKDAYS($A$2,$A4,$Z$1:$Z$10))

regards, barry
0
 

Author Closing Comment

by:dgore1
ID: 35191426
Great help as always!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now