Solved

Date Calculation in Excel 2007

Posted on 2011-03-20
11
570 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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