Calculate evening- and night-shift hours from working time

[jeppis]

Is there any function to calculate this:

- starting time 05:12 am (for example in A1)
- ending time 23:38 pm (for example in B1)

And then the problem:
How do I separate evening and night working time what has been done between the total working time mentioned above?

1. night-shift time (between 00:00am-06:00am and 23:00pm-24:00pm)
2. evening-shift time (between 18:00pm-23:00pm)

Any suggestions?

 

[dcardno]

Do you want the total regular time, evening time, and night shift time in different cells, or do you want to calculate the total number of paid hours with a given premium (or rate differential) for evening and night shifts? The first will be possible (but messy) with several complicated Excel formulas; the second will also be possible, but may cause brain damage - I would recommend a "User Defined Formula" created in VBA.

 

[jeppis]

dcardno:
Yes for your first question, total regular time, evening time and night time should all be separated in different cells.

After trying to find out solution to this problen long enough I don't have to worry about brain damage anymore, I can now happily inform that I have it already.

About VBA: not so familiar to me, any help for that or any other solution to this question?

 

[barry houdini]

Might not be the most efficient way but perhaps try this....

Book1
	A	B	C	D	E	F
1
2	start	finish	regular	evening	night
3	05:12	23:38	12:00	05:00	1:26
4	23:25	23:20	12:00	05:00	6:55
5	23:20	23:25	00:00	00:00	0:05
6	18:44	19:26	00:00	00:42	0:00
7	19:26	18:24	12:00	03:58	7:00
8	04:25	10:13	04:13	00:00	1:35
9	05:12	18:23	12:00	00:23	0:48
10	10:13	22:10	07:47	04:10	0:00
11	19:23	01:01	00:00	03:37	2:01
12	23:44	05:18	00:00	00:00	5:34
13	23:01	08:13	02:13	00:00	6:59
14	23:40	19:00	12:00	01:00	6:20
15
Sheet3

Formula in C3 copied down

=(A3>B3)*MEDIAN(0,B3-1/4,1/2)+MAX(0,MIN(3/4,B3+(A3>B3))-MAX(1/4,A3))

formula in D3 copied down

=(A3>B3)*MEDIAN(0,B3-3/4,5/24)+MAX(0,MIN(23/24,B3+(A3>B3))-MAX(3/4,A3))

formula in E3 copied down

=B3-A3+(A3>B3)-C3-D3

 

[dcardno]

I was going to start with a formula like Barry has supplied - except that mine would have been much more verbose, and much harder to understand. Then I thought of a different approach. A couple of years ago I wrote a little UDF to interpolate a Y-Value for a new X-Value and a series of known X- and Y- Values. I will post the code for the UDF in the next post, and the resulting s/sheet below.

The Regular, Evening and Night hours are all 'interupted linear' functions - given a schedule running along the X-Axis the cumulative hours (of any type) will be either a line rising at 45 degrees or a horizontal line (eg - between 6:00 and 23:00 the cumulative hours are flat, while first the regular, and then the evening hours increase. By supplying the cummulative hours over a 48 hour period at each change in shift-type (when the accumulation rates come into effect or go out of effect) I could supply the known X- and Y-values to the interpolate function. The times of the change from each type of hour worked are held in a table (in the range B4:E11) - this allows them to be changed fairly rapidly - if, for example you decided that 'night hours' should extend to 06:30.

The total hours by type from 0:00 on "day 1" to quitting time, less the cumulative hours by type from 0:00 to the starting time will be the actual hours, by type, worked in the shift.

The resulting s/sheet is shown below - note that we have to test for the end time being earlier than the start time; this implies that the shift has ended on the following day, so we add "1" representing 24 hours. If shifts can be longer than 24 hours this routine will interpret them as being only from the start to the end time (ie - chopping off 24 hours), so the "day 1" and "day 2" times will have to be made explicit.

Book1
	A	B	C	D	E	F	G
1
2			Shift	Cum. Hours
3			Times	Regular	Evening	Night
4		day 1	0:00	0.00	0.00	0.00
5			6:00	0.00	0.00	6.00
6			18:00	12.00	0.00	6.00
7			23:00	12.00	5.00	6.00
8		day 2	6:00	12.00	5.00	13.00
9			18:00	24.00	5.00	13.00
10			23:00	24.00	10.00	13.00
11			0:00	24.00	10.00	14.00
12
13		Start	Finish
14		5:45:00 PM	11:55:00 PM	0.25	5.00	0.92
15		5:45:00 PM	2:00:00 AM	0.25	5.00	3.00
16		5:00:00 AM	2:00:00 AM	12.00	5.00	4.00
17		5:00:00 AM	5:00:00 AM	12.00	5.00	7.00
18
Sheet1

VBA code for the Interpolate function to follow...

 

[dcardno]

As promised (or threatened...) the Interpolate Function code...

<font face=Courier New>

<SPAN style="color:#00007F">Function</SPAN> Interpolate(X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>, XRange <SPAN style="color:#00007F">As</SPAN> Range, YRange <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">Optional</SPAN> InterpType <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> = 0) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

<SPAN style="color:#007F00">' Function returns interpolated value of "Y" for supplied X, based on supplied ranges of known</SPAN>
<SPAN style="color:#007F00">' X and Y values.  Optional "InterpType" argument controls how function responds to X arguments</SPAN>
<SPAN style="color:#007F00">' outside the known X values:</SPAN>
<SPAN style="color:#007F00">'   If InterpType is:</SPAN>
<SPAN style="color:#007F00">'   = 0: returns an error (default)</SPAN>
<SPAN style="color:#007F00">'   = 1: extrapolates based on last two X-Y pairs (either two highest or two lowest)</SPAN>
<SPAN style="color:#007F00">'   = 2: extrapolates based on first and last X-Y pair (full range of supplied values)</SPAN>
<SPAN style="color:#007F00">'   = 3: extrapolates based on first or last X-Y pair and the origin (0-0)</SPAN>
<SPAN style="color:#007F00">'    Other values return an error</SPAN>

<SPAN style="color:#007F00">' Function is based on the "InterpolateVLOOKUP" UDF developed by Myrna Larson, and published in the</SPAN>
<SPAN style="color:#007F00">' Excel Expert's E-Letter, which can be found at: http://www.j-walk.com/ss/excel/eee/eee002.txt</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> blErr      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> iBase      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> iComp      <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> i          <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> dX0        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> dX1        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> dY0        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> dY1        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Temp       <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>

<SPAN style="color:#007F00">' ensure that XRange is sorted ascending so match function result is reliable</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> XRange.Count - 1
    <SPAN style="color:#00007F">If</SPAN> XRange(i + 1) < XRange(i) <SPAN style="color:#00007F">Then</SPAN> blErr = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Next</SPAN>

Temp = WorksheetFunction.Match(X, XRange, 1)
<SPAN style="color:#00007F">If</SPAN> IsError(Temp) <SPAN style="color:#00007F">Then</SPAN>
    Interpolate = <SPAN style="color:#00007F">CVErr</SPAN>(Temp)
<SPAN style="color:#00007F">Else</SPAN>
    iBase = <SPAN style="color:#00007F">CInt</SPAN>(Temp)
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> iBase
        <SPAN style="color:#00007F">Case</SPAN> 0
            <SPAN style="color:#007F00">'match function did not find a match - X =< min(XRange)</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> InterpType
                <SPAN style="color:#00007F">Case</SPAN> 0
                    <SPAN style="color:#00007F">If</SPAN> X = XRange(1) <SPAN style="color:#00007F">Then</SPAN>  <SPAN style="color:#007F00">'X is not "less than" lowest value in range, it is equal</SPAN>
                    iBase = 1
                    <SPAN style="color:#00007F">Else</SPAN>                   <SPAN style="color:#007F00">'X is IS < lowest value in range - represents an error</SPAN>
                    blErr = <SPAN style="color:#00007F">True</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">Case</SPAN> 1
                    iBase = 1
                    iComp = 2
                <SPAN style="color:#00007F">Case</SPAN> 2
                    iBase = 1
                    iComp = XRange.Count
                <SPAN style="color:#00007F">Case</SPAN> 3
                    iBase = 1
                    iComp = 0
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
                    blErr = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> XRange.Count
            <SPAN style="color:#007F00">'match returns last value in XRange, so X is >= max(XRange)</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> InterpType
                <SPAN style="color:#00007F">Case</SPAN> 0
                <SPAN style="color:#00007F">If</SPAN> X <> XRange(XRange.Count) <SPAN style="color:#00007F">Then</SPAN>  <SPAN style="color:#007F00">'X is IS > highest value in range - represents an error</SPAN>
                    blErr = <SPAN style="color:#00007F">True</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">Case</SPAN> 1
                    iComp = iBase - 1
                <SPAN style="color:#00007F">Case</SPAN> 2
                    iComp = 1
                <SPAN style="color:#00007F">Case</SPAN> 3
                    iComp = 0
                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
                    blErr = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#007F00">'match returned position of value next larger than X, within XRange</SPAN>
            iComp = iBase + 1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    dX0 = XRange(iBase)
    dY0 = YRange(iBase)
    <SPAN style="color:#00007F">If</SPAN> X = dX0 <SPAN style="color:#00007F">Then</SPAN>
        Interpolate = dY0
    <SPAN style="color:#00007F">Else</SPAN>
        dX1 = XRange(iComp)
        dY1 = YRange(iComp)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> blErr = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Err.Raise Number:=11
        Interpolate = <SPAN style="color:#00007F">CVErr</SPAN>(Err)
        <SPAN style="color:#007F00">'return "div0" error, to ensure that error propagates through s/sheet calculations</SPAN>
        <SPAN style="color:#007F00">'XL 2003 does *not* return an error, but coerces the value to 0 (double)</SPAN>
        <SPAN style="color:#007F00">'unless the Interpolate data type is set to Variant.</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        Interpolate = (X - dX0) / (dX1 - dX0) * (dY1 - dY0) + dY0
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

</FONT>

 

[jeppis]

Thank you both dcardno and barry.
I'll get back to you in here as soon I have time to try this your solutions.

I think that now my brain damage is maybe getting better, at least I wish to believe so.

 

[jeppis]

Hi you all.
Finally I had time to continue my project, sorry that this answer comes so late.

Thanks barry, your resolution worked just like it should.

Dcardno:
I have a problem with interpolate-function;
Function =interpolate(IF($C14<=$B14,$C14+1,$C14),$C$4:$C$11,D$4:D$11) - interpolate($B14,$C$4:$C$11,D$4:D$11)

where
D14 = interpolate(
X = IF($C14<=$B14,$C14+1,$C14)
XRange = $C$4:$C$11
YRange = D$4:D$11
InterpType =
)
- interpolate(
X = $B14
XRange = $C$4:$C$11
Y-Range = D$4:D$11
InterpType =
)

function returns error to me. What am I doing wrong?
(I'm feeling this strange pressure in my head..)

 

[dcardno]

I can recreate that error (although it may be from a different cause ) if the values in the "second day" portion of the reference range are incorrect. Note that the values running from C4 to D11 are 0:00 / 6:00 / 18:00 / 23:00 / 30:00 / 42:00 / 47:00 / 48:00 - it's just that the formatting makes the second day look like 6:00 / 18:00... The "interpolate" function requires that the "X values" increase monotonically (that's just a fancy way of saying that each X value is equal to or greater than the prior X value) - if not, the function will return an error.

In the sample I posted the values in C8:C10 were a formula that added one to the coresponding value above - but I didn't highlight that fact. If you still have the problem after you make that change (or if that isn't the problem at all), post again and I will try to figure out what is going on...

 

[Riddler77]

Hi Barry, I know it has been many years since you posted this.

I have used your formula's above to determine Day, Evening and Overnight Shifts.
The Hours are -
Day Shift 06:00 -> 20:00
Evening Shift 20:00 -> 0:00
Overnight Shift 0:00 - > 6:00

I have modified your formulae for Day and Eveing however I cannot get the overnight to function ie Cell E3 as above.

Could you please assist or check the E3 formula for correctness?

Thanks

Darren