S-Curve & Planning....

[emirmansouri]

Hi there,

I am designing a S-Curve Template and require some help to achieve my target.
I will try to explain what is required and if anyone can help I would really appreciate it.

I have an S-Curve Template Built into Excel and the Data Table is from a MS Project Schedule.
The data from the Schedule to be used for the S-Curve is the Date & Time an activity Starts & Ends.

A2 - (Start Date & Time) 14/05/2012 06:00:00 AM
B2 - (End Date & Time) 17/05/2012 07:00:00 AM

Our Calendar: 24Hours / 7Days
Shift Starts at 06:00:00 AM (Example: 14/05/2012 6:00:00 AM)
Shift Ends at 06:00:00 AM (Example: 15/05/2012 6:00:00 AM)
Total Hours = 24 Hours (for the 14/05/2012)

What is required is the amount of Hours worked for each day between 6:00am & 6:00am the next day:

Column (A) Start Column (B) End Column (C,D,E) Result
14/05/2012 06:00:00 AM till 14/05/2012 07:00:00 AM = 1 Hour for 14/05
14/05/2012 07:00:00 AM till 15/05/2012 06:00:00 AM = 23 Hours for 14/05

Now this is where it will come in handy, what if there is work spanning 3 days as shown below:

Column (A) Start Column (B) End
15/05/2012 06:00:00 AM till 17/05/2012 07:00:00 AM =

Column (C,D,E) Result
24 Hours on 15/05/2012 & 24 Hours on 16/05/2012 & for the 17/05/2012 1Hour

Now what if the work spanned 10 Days?

If someone can help me with this it will be greatly appreciated

This will help with setting the Weighting system for the S-Curve and all that is left is to smooth-en the curve.

 

[emirmansouri]

Anyone please help..

 

[Robert Mika]

Your question is unclear.
is this what youa after?

Excel Workbook
	A	B	C
1	14/05/2012 06:00	14/05/2012 07:00	1
2	14/05/2012 07:00	15/05/2012 06:00	23
3	15/05/2012 06:00	17/05/2012 07:00	49
Sheet1

or do you need to have one cell per one day and need formula which split your datas day by day and then calculate the hours?

 

[emirmansouri]

Hi Mika,

This is an example of what im looking for if possible.

Thank you for your quick reply:

I want the specific hours in a day (All shifts start at 06:00am and finish the next day 06:00am

Serial Number 6 is a good example.
1) They work from 19:00 to 06:00 = 11 Hours (For the 15/05/2012)
2) They then work from 06:00am 16/05/2012 till 06:00am on the 17/05/12 = 24Hours
3) They also work for one more hour from 07/05/2012 06:00 am till 07/05/2012 07:00

So fro cell with:
15/05/2012 = 11 Hours
16/05/2012 = 24 Hours
17/05/2012 = 1 Hour

If this is possible it would be very helpful.

Thanks again

 

[emirmansouri]

Urgently required.......

Please help..

 

[emirmansouri]

Anyone?
I know its a challenge but im sure someone already has done this before!
Anyway your help on this would be greatly appreciated.

 

[shg]

There's a workbook with an example on the Across Days tab at https://www.box.com/shared/7xm2kt408n

 

[emirmansouri]

Thank you so much I will check it out as soon as Im at the office and report back.
Again thanks a bunch ; )

 

[shg]

You're welcome.

The workbook has macros that are used on another tab. You need not enable them.

 

[emirmansouri]

Thats exactly what I needed, whoom ever you are I am soo greateful for this. You have made my day.