Timesheet: Calculate Time within a period when crossing midnight

Hi All,

I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).

There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.

I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days.

I have attached a spreadsheet to help show the problem.
Any help would be greatly appreciated? I look forward to seeing your possible solutions!

Welcome to the Board.

Using your example perhaps:

E10: =IF($C10>=$B10,MIN($C10,UpperBound)-MAX($B10,LowerBound),MAX(0,$C10-LowerBound))
F10: =$D10-$E10

Note I would also simplify

D10: =MOD($C10-$B10,1)

(assumes no shift ever exceeds 24 hrs in total)

Apologies, I thought In Core represented day shift and Out of Core night shift thus the formulae provided previously populates E with day shift hours and F with night shift hours ... you could just swap the headers (or the formulas) ?

OK, I would suggest you post another sample file with a few variations of shifts and clearly identify the expected results.
In truth it's always best to start with the real problem rather than a simplified version thereof - what works for one scenario may well fail to work for another...

I think we're going to need a little more info in terms of how Sundays & Public Holidays are accounted for... ie if a shift starts half way through a Sunday and ends say on a Monday is the Sunday rate applied to the entire shift or only up until Midnight at which point the Monday hours are rated according to the standard shift pattern(s) ? Where are you accounting for Public Holiday hours in your results matrix ? What impact does exceeding 60 hours in the week have on your calculations ?

In basic terms... ignoring the issues of breaks / Sundays etc... things could be simplified such that:

G11: =24*(MOD(F11-C11,1)-MOD(E11-D11,1))
copied down

P11: =G11/24
copied down

H11: =IF(COUNTIF($C$47:$E$56,$B11),"PH","")
copied down
(note it may make sense to name your holiday range)

I11: =IF(COUNT($C11,$F11)=2,24*MAX(0,MIN(OrdFinish,$F11)-MAX(OrdStart,IF($F11<$C11,0,$C11))),"")
copied down

J11: =IF(COUNT($C11,$F11)=2,24*MAX(0,MIN(PenFinish,$F11)-MAX(PenStart,IF($F11<$C11,0,$C11))),"")
copied down
as I11 but using PenStart / PenFinish

K11: =I11-SUM(I11:J11)
copied down

Again the above are by no means the finished article as there are still plenty of exceptions to be accounted for (breaks, Sundays, Public Holidays, >60 hrs etc etc)... not to mention the possibility of the shift patterns changing and themselves crossing midnight (is this likely?)

A good way to do this (given the complexity) would be for you to put together a further file that handles all various connotations and outline the expected results (calculated manually) for each variation - this then allows others to determine the logic and guarantee the solutions offered meet requirements.

In truth this may require someone with greater expertise than myself - I have someone in mind and will ask them to look at this thread for you but to reiterate a further sample in even greater detail than the last is sure to help achieve a speedier resolution.

Originally Posted by daddylonglegs

OK, perhaps my comments weren't clear but the formula I suggested will calculate the hours worked within a defined period, even if the shift starts one day and ends the next.

See the attached sheet.

In B10:C20 I have put some formulas that generate random shifts, this is just for testing purposes. If you find that useful you can press F9 to re-generate or just overwrite these with your own fixed times.

The formula in D10 calculates the hours between the Lower and Upper bounds. The formula in E10 calculates other hours, i.e. those outside the time period. LB needs to be earlier than UB

Obviously this gets more complicated if you want to take the day of the week into account.....

regards, daddylonglegs

Very useful daddylonglegs. My apologies but I am lost how to extend this spreadsheet & formula if I need to meet a second additional conditional time range.

To provide context,I have used your spreadsheet to work out the hours for a given piece of electrical equipment (e.g. pump motor) or lighting circuit operated between some start/finish time per day (your random time generator is perfect). This may cross midnight e.g. security task lighting operating from 10pm to 7 am. Hence, this spreadhseet uploaded works a treat. For my purpose, LB and UB would represent the hours under a certain tariff time.

Electricity tariffs are broken into peak, shoulder and off-peak hour ranges. So your initial solution essentially does this with ease.
e.g.
Peak charges apply 2pm to 8pm weekdays.
Shoulder 1 7am to 2pm weekdays
Shoulder 2 8pm to 10pm weekdays
Off-peak all other times


I now wish to lay on top of this to work out the operating hours that fall between LB and UB peak charge times (2pm to 8pm), subject to another range criteria (say PVL and PVH), where PVL is say 8am and PVH is 5pm - the key daylight hours for Solar Photovoltaic generation.

Thus I know if I install a PV system, and I have my lights on from 8am to 11pm, only the time between 2pm (LB) and 5pm (PVH) of the peak tariff charge time hours would be offset by the PV system and no more.