Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date Calculation in Excel 2007

Posted on 2011-03-20
11
Medium Priority
?
577 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

721 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