Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date Calculation in Excel 2007

Posted on 2011-03-20
11
Medium Priority
?
578 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 8

Accepted Solution

by:
wchh earned 1000 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 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

963 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