Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date Calculation in Excel 2007

Posted on 2011-03-20
11
Medium Priority
?
580 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

580 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