Need to calculate start and end dates based upon number of days (some sort of gantt)

[mpatino]

Hi guys,

I was wondering if someone could help me put a VBA together that will help me calculate start and end dates based on total number of days of certain training topics, this is an example of my spreadsheet, just as a side note, I will be providing the first start date, but then want all the other dates to be calculated automatically, I would also need to exclude weekends (Sat and Sun).

	A	B	C	D	E	F	G	H	I	J	K
1	Milestone	Task	Owner	Backup	Hours required to complete	Start Date	End Date	Pre-Req	% Completed	Status	Comments
2	Knowledge transfer	1	Junior	Anita	6	05/01/2014
3	Knowledge transfer	2	Junior	Anita	4
4	Knowledge transfer	3	Junior	Anita	8
5	Knowledge transfer	4	Junior	Anita	7
6	Knowledge transfer	5	Junior	Anita	5		Need to calculate end date based upon E8 results
7		Total hrs			30
8		Total days			Existing Formula =roundup((E7/8),0) = 4 days
9	Knowledge transfer	6	Louise	Mark	8	Need to calculate start date based upon G6 results
10	Knowledge transfer	7	Louise	Mark	2
11	Knowledge transfer	8	Louise	Mark	4
12	Knowledge transfer	9	Louise	Mark	6
13	Knowledge transfer	10	Louise	Mark	30
14	Knowledge transfer	11	Louise	Mark	9		Need to calculate end date based upon E16 results
15		Total hrs			59
16		Total days			Existing Formula =roundup((E45/8),0) = 8 days

<tbody>
</tbody>

I hope this makes sense.

thank you,

 

[ace19852]

I've have a look at your data and come up with a formula based solution - download file on link

Download Calculate date based on start date.xlsx from Sendspace.com - send big files the easy way

In the two right most column there is are formulas showing the day of the week inline to the dates and I noted in your example data above 05/01/14 is a Sunday I assume that was an error as you say you wanted to exclude Saturdays and Sundays

If you want to keep the formulas relative you will have to insert and delete lines as necessary to build the timetable .

Cheers see what you think while you wait for a VBA expert to jump in !!!

 

[Peter_SSs]

I've have a look at your data and come up with a formula based solution - download file on link ...

I haven't looked at the link but could you post any formulas, code or screen shots directly here?

Are you aware of #7 of the Forum Rules, especially paragraphs 3 & 4?

 

[mpatino]

hey this formula works really good, I had to copied and paste and modify the cell number, but I was able to accomplished exactly what I needed. Thanks a lot for your time helping me out here.