how do I add portion of days for scheduling

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

notanexcelguru - 02 May 2008 17:28 GMT

Below is a schedule for a machine. For example, JOB1 is equal to 3.65 of a
day and it will start on 5/1 and finish on 5/6. I have a formula for cell C2
that is =WORKDAY(L3,I3,$K$1) and I copied it down for all of column
C(K1=5/26, Memorial day), then cell B3=C2. The only problem is the
calculation is not keeping track of the portion of the day already consumed.
It is starting new with each line as you can see below where jobs 4-17 show
they will all finish on the same day. is there away to have excel actually
keep track of the portion of the day consumed?

JOB A B C
1    % of day    Machine Start Machine End
2    3.65    5/1    5/6
3    1.25    5/6    5/7
4    1.301    5/7    5/8
5    0.4592    5/8    5/8
6    0.4592    5/8    5/8
7    0.359    5/8    5/8
8    0.359    5/8    5/8
9    0.359    5/8    5/8
10    0.359    5/8    5/8
11    0.4676    5/8    5/8
12    0.4258    5/8    5/8
13    0.359    5/8    5/8
14    0.4592    5/8    5/8
15    0.4592    5/8    5/8
16    0.4592    5/8    5/8
17    0.526    5/8    5/8
18    1.7352    5/8    5/9
19    1.0604    5/9    5/12
20    1.0604    5/12    5/13
21    0.9936    5/13    5/13
22    1.027    5/21    5/22

Reply to this Message

ryguy7272 - 02 May 2008 18:46 GMT

Use this macro:
Sub PropFill()

Dim c As Range
Dim v As Single
Dim s As Shape

For Each c In Selection
If c.Value >= 0 And c.Value <= 1 Then

v = c.Value
Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
c.Left, c.Top, c.Width * v, c.Height)
s.Fill.Visible = msoTrue
s.Fill.ForeColor.SchemeColor = 44
s.Fill.Transparency = 0.67
End If
Next c
End Sub
(select cells before running it)

And fill down starting in cell D1:
=C1-B1-(A1/100)

Regards,
Ryan---

Signature

RyGuy

> Below is a schedule for a machine. For example, JOB1 is equal to 3.65 of a
> day and it will start on 5/1 and finish on 5/6. I have a formula for cell C2
[quoted text clipped - 28 lines]
> 21 0.9936 5/13 5/13
> 22 1.027 5/21 5/22

Reply to this Message