Split from and to time into fractions

[valuesim]

Hi,
When having to and from time, typically when working shift, the working hours
per hour is what I am looking for.
Fromdate Todate
07.03.2011 00:00 07.03.2011 07:00
08.03.2011 12:00 09.03.2011 00:00
07.03.2011 22:30 08.03.2011 07:00
09.03.2011 00:00 10.03.2011 00:00
10.03.2011 00:00 11.03.2011 00:00
11.03.2011 15:00 11.03.2011 22:45

starting 22.30 gives 0.5 hours in the hour from 22 to 23 and then 1 hour per hour until 0700 next day.

The shift might start at any given time within an hour and end the same way when the shift end. Within a week it is 168 hours and how do you manage to go from the from and to hours to splitting the shift into fractions of e.g. one hour parts starting the hour the shift starts?

 

[AlphaFrog]

Hi and welcome to the board.

I'm not sure I follow what you are asking, but here's a guess.

<br />

Book1
	A	B	C
1	Fromdate	Todate	Decimal hours
2	07.03.2011 00:00	07.03.2011 07:00	7
3	08.03.2011 12:00	09.03.2011 00:00	12
4	07.03.2011 22:30	08.03.2011 07:00	8.5
5	09.03.2011 00:00	10.03.2011 00:00	24
6	10.03.2011 00:00	11.03.2011 00:00	24
7	11.03.2011 15:00	11.03.2011 22:45	7.75
8		Total:	83.25
Sheet1

Cell Formulas
Range	Formula
C2	=(B2-A2)*24
C3	=(B3-A3)*24
C4	=(B4-A4)*24
C5	=(B5-A5)*24
C6	=(B6-A6)*24
C7	=(B7-A7)*24
C8	=SUM(C2:C7)

How Excel Stores Dates And Times

 

[valuesim]

Hi, thanks for replying, I'll elaborate. The numbers of hours between is fine. But if you have many shifts you like to see how the hours is distributed through a given time periode e.g. a week with 168 hours. Using a pivot tabel is nice for that. That means for the first hour the work starts you might have a fraction of an hour in that hour of the day. I guess the best way would be to be able to add a new line for every hour of the shift adding the time amount in that hour.

21.03.2011 22:30 22.03.2011 07:00
We start in the 22 hour with 0.5 hours
next line would be 1 hour
next 1 hour
and so on until
0600 and 1 hour

The pivot table then gives you a possibility to how many hour have been worked in total in any given hour of the day or hour of the week.
But how to go from from and to and generate new lines for the time worked within the hours is kind of the problem.

br
jm

 

[valuesim]

If we operate a factory with shift wokers and Joe Doe
start 11.03.2011 15:00 to 11.03.2011 22:45 = 7.75 hours

We like to transform into this format
Joe Doe 15:00 to 16:00 1 hour
Joe Doe 16:00 to 17:00 1 hour
Joe Doe 17:00 to 18:00 1 hour
..
Joe Doe 22:00 to 23:00 0.75 hour

We can then use the pivot to know how many man hours do we have present in any given hour of the year, month, week and so on..

Intially we have something like this
Joe Doe start 11.03.2011 15:00 to 11.03.2011 22:45
any suggestion on how to transform?
Any input is appreciated.
br
jm