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).


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


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.