Calculate evening- and night-shift hours from working time

jeppis

New Member
Joined
Oct 6, 2006
Messages
4
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 6, 2006
Messages
4
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Might not be the most efficient way but perhaps try this....
Book1
ABCDEF
1
2startfinishregulareveningnight
305:1223:3812:0005:001:26
423:2523:2012:0005:006:55
523:2023:2500:0000:000:05
618:4419:2600:0000:420:00
719:2618:2412:0003:587:00
804:2510:1304:1300:001:35
905:1218:2312:0000:230:48
1010:1322:1007:4704:100:00
1119:2301:0100:0003:372:01
1223:4405:1800:0000:005:34
1323:0108:1302:1300:006:59
1423:4019:0012:0001:006: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

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFG
1
2ShiftCum. Hours
3TimesRegularEveningNight
4day 10:000.000.000.00
56:000.000.006.00
618:0012.000.006.00
723:0012.005.006.00
8day 26:0012.005.0013.00
918:0024.005.0013.00
1023:0024.0010.0013.00
110:0024.0010.0014.00
12
13StartFinish
145:45:00 PM11:55:00 PM0.255.000.92
155:45:00 PM2:00:00 AM0.255.003.00
165:00:00 AM2:00:00 AM12.005.004.00
175:00:00 AM5:00:00 AM12.005.007.00
18
Sheet1


VBA code for the Interpolate function to follow...
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 6, 2006
Messages
4

ADVERTISEMENT

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

New Member
Joined
Oct 6, 2006
Messages
4
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

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
I can recreate that error (although it may be from a different cause :confused:) 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

New Member
Joined
Jun 14, 2017
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,463
Messages
5,658,928
Members
418,476
Latest member
Tristram_ZX81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top