Hi Guys,

I'm trying to add 2 columns in excel for Work Breakdown Structure

for E.g

* - current cell formatting

              *(number)         *(date)             *(date)
                 A                 B                       C
                 Duration      Start Date          End Date
1              3                   21/10/09          24/10/09
2              2.5                21/10/09         24/10/09 and not 23/10/09 hh:mm

A1 + B1 = C1 is fine, however how do i get C2 to display '24/10/09' ??
i.e anything that has '.5' is carried over to the following day.

what formula can I use...

I'm using Excel 2007                                     

If you mean any decimal part .5 and above and any decimal part less than .5 rounded down, you could use the ROUND function, e.g. this in cell C1:
=ROUND(A1,0)+B1
If you want any fraction rounded up to a whole day, use CEILING or ROUNDUP.

Hi Jonathan,

When I used the Round formula, the result column now has  1/19/1900

Hos is that???

You're adding the wrong values. In your example, this formula would be in cell C1:
=ROUND(A1)+B1

Hi Jonathan,

I've tried what you have suggested, but it says "you have entered too few arguments for this function",

So i'm kinda stumped.

Randyrr

Oops. Use the first formula I posted, not that last one. The zero is necessary to tell Excel what decimal place to round on.

Thank you Jonathan,

It worked beautifully!!

I have another question, quite similar too...

If i have:
                                         (date) 
                                            A                                    B                       C
            1                   9/07/09 - 9/14/07           ????? - ??????                                       
                                                                         
    How do i get cell B2, to display the range 9/15/09 - 9/22/09  and so on?                                                                            

Do you really need eight-day ranges rather than weeks?
I suggest using a (hidden?) cell with the beginning date. That way you can change that cell and the range will adjust. I used cell A17 in this example.
Put this in A1:
=TEXT($A$17+ 8*(COLUMN()-COLUMN($A1)),"m/d/yyyy")&" - "&TEXT($A$17-1+8*(COLUMN()-COLUMN($A1)+1),"m/d/yyyy")
Then you can just copy it to the right.
 
 

Ohhhh,

Sorry Jonathan, I need columns that displays a week ( formatted in ranges, e.g 9/17/09 - 7/24/09 and so on)for 26 weeks (1 week per column)... 


Randy

All your examples (including 9/17/09 - 7/24/09) show eight-day periods...
For one-week periods, again using A17 as the start date, use this formula in A1 and then just copy it across the next 25 columns:
=TEXT($A$17+ 7*(COLUMN()-COLUMN($A1)),"m/d/yyyy")&" - "&TEXT($A$17-1+7*(COLUMN()-COLUMN($A1)+1),"m/d/yyyy")
If you start in some cell other than A1, change the $A1 reference to that of the starting cell.

For two-digit years, just change the "m/d/yyyy" format string to "m/d/yy" in the TEXT functions.

Waw Jonathan,



Sorry, i forgot, i made the adjustments already when u told me the first time, i was just unable to post this to let u know....

This has worked beautifully, I can't thank you enough!!!



Thanks again!!



randyrr







(I hope u don't mind, should i come again with other questions... :) )