+ Reply to Thread
Results 1 to 14 of 14

Timesheet: Calculate Time within a period when crossing midnight

  1. #1
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    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!
    Attached Files Attached Files
    Last edited by G0liath02; 05-21-2009 at 08:31 PM. Reason: Solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    re: Timesheet: Calculate Time within a period when crossing midnight

    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)

  3. #3
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Timesheet: Calculate Time within a period when crossing midnight

    Hi DonkeyOte,

    Thanks for your reply.
    I think that your formula calculates the answer, but has the same problem with shifts that fall across midnight.

    Perhaps I haven't defined my problem clearly enough.

    Continuing with the attached example:

    Upper Bound: 22:00 (10pm)
    Lower Bound: 7:30 (7.30am)

    I am trying to determine the number of hours worked on night shift between these times ie. 10pm-7.30am. This is to be displayed in E10.

    Thanks again.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    re: Timesheet: Calculate Time within a period when crossing midnight

    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) ?
    Last edited by DonkeyOte; 05-15-2009 at 02:27 AM.

  5. #5
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Timesheet: Calculate Time within a period when crossing midnight

    I would love it if it were that simple. And will have a big laugh at myself if it turns out to be something like that!!

    Unfortunately I have 3 different time periods i need to calculate.

    1 Ord Mon-Sat 7:30 - 20:00
    2 Penalty Mon-Sat 20:00 - 0:00
    3 Night Mon-Sat 24:00 - 7:30

    Any ideas?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    re: Timesheet: Calculate Time within a period when crossing midnight

    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...

  7. #7
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Timesheet: Calculate Time within a period when crossing midnight

    Ok,

    This is an example of the actual template I am working on.

    If it becomes too complex - do you think it would be better approached with VBA and a custom function?

    Thanks again for your help it is much appreciated!
    Attached Files Attached Files

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    re: Timesheet: Calculate Time within a period when crossing midnight

    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.

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    re: Timesheet: Calculate Time within a period when crossing midnight

    This is a generic formula to get hours between LowerBound and Upperbound, this period shouldn't cross midnight, i.e. Lowerbound must be lower than UpperBound

    =(B10>C10)*MEDIAN(0,C10-LowerBound,UpperBound-LowerBound)+MAX(0,MIN(UpperBound,C10+(B10>C10))-MAX(LowerBound,B10))

    Obviously you can get hours outside that period by simply deducting the result of that formula from the total hours worked.

    This formula works for all time periods up to 24 hours. Obviously if you also want to take specific days or holidays into account then that will complicate matters but it should still be feasible.

    regards, daddylonglegs

  10. #10
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Timesheet: Calculate Time within a period when crossing midnight

    Thank you both for your responses I do appreciate your help.
    However I believe both your suggestions do not solve the core problem I was having, which is trying to calculate a shift that goes over midnight.

    I will look at putting together a more detailed sample. However the core problem remains the same - how to calculate the amount of time which falls in a penalty period when that time crosses midnight.

    I agree it's complex and perhaps a custom vba function would be better for this. I could code it but am not sure what logic to use to calculate the time when a shift crosses midnight.

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    re: Timesheet: Calculate Time within a period when crossing midnight

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    re: Timesheet: Calculate Time within a period when crossing midnight

    Daddy longlegs - that looks fantastic!

    Thank you very much to both of you for your help.With the formula's you've both given me within this thread I should be able to proceed. I'll still have to factor in Sunday's and public holidays.

    I'll look into this at work tomorrow and see how I go.

  13. #13
    Registered User
    Join Date
    05-14-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Timesheet: Calculate Time within a period when crossing midnight

    I was able to extend the formula given to me by daddy long legs to remove breaks taken within a specific time period. I also changed it to look to see if the day was a public holiday and to round the time to the nearest 5 minute interval.

    Thanks very much DonkeyOte and DaddyLong legs for your help!

    Formula = =IF($H12="PH",0,IF($A12="Sun",0,(ROUND((($C12>$F12)*MEDIAN(0,$F12-OrdStart,OrdFinish-OrdStart)+MAX(0,MIN(OrdFinish,$F12+($C12>$F12))-MAX(OrdStart,$C12)))/"00:05","00:05")*"00:05"-(ROUND((($D12>$E12)*MEDIAN(0,$E12-OrdStart,OrdFinish-OrdStart)+MAX(0,MIN(OrdFinish,$E12+($D12>$E12))-MAX(OrdStart,$D12)))/"00:05","00:05")*"00:05"))*24))

  14. #14
    Registered User
    Join Date
    11-01-2013
    Location
    australia
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Timesheet: Calculate Time within a period when crossing midnight

    Quote Originally Posted by daddylonglegs View Post
    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.
    Last edited by drdpr; 11-04-2013 at 02:34 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1