View original (French)

On-call hour calculation

Geoffrey_ Posted messages 12 Status Member -  
Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   -
Hello,
First of all, I would like to thank the "Comment ça marche" community as I am a novice (a real one) in Excel, and I have achieved a lot thanks to you, so THANK YOU.

I am reaching out for your help in a professional context this time...

To give you some background,
In my department, on-call duty is in effect, and there is no software for calculating the overtime generated by it.

Let me explain the concept:
The on-call duty runs from Thursday at 5:00 PM to Thursday at 5:00 PM the following week.
During this time, we may be called out for troubleshooting.
If we go out between 5:00 PM and 7:30 AM, and we do not have at least 11 hours of rest, we are entitled to additional hours in difference (meaning, only 8 hours of consecutive rest: 11 - 8 = 3 hours of rest)
This is effective from 5:00 PM to 7:30 AM on weekdays, and the entire weekend (Friday at 5:00 PM to Monday at 7:30 AM)

Another additional problem is that if there is not at least 1 hour of rest between two interventions, it is considered without interruption (like a single intervention without a break).

I am looking to create a calculation in which I could enter the hours we were called, and it would give me the rest hours we are entitled to.

I tried making each row correspond to a quarter-hour, and the columns the days, but I have no idea what to do next....

Can you please help me?
I am using the Excel / Word 2007 suite

Configuration: Windows 7 / Firefox 35.0

13 answers

  1.
    Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Yes, it is possible to assist you.
    Could you upload a file with some intervention data to https://www.cjoint.com/ and post the link.

    --
    Best regards.
    The Penguin
    0
    1. Geoffrey_ Posted messages 12 Status Member
       
      Hello,
      I don't understand the question...

      Best regards
      0
  2. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Your explanations in the first message are not enough, and since it seems you have started a file, it would be good to make it available.
    So please upload it to https://www.cjoint.com/ and post the link.

    Note: for proper tracking, please make sure to use Reply to topic

    --
    Regards.
    The Penguin
    0
  3. Geoffrey_ Posted messages 12 Status Member
     
    Hello,
    Attached is the file I started to create...
    https://www.cjoint.com/c/EJCl5yOOqsd

    To be more precise,
    when we are on duty, we need to have at least 11 hours of consecutive rest from Monday 5:00 PM to Tuesday 7:30 AM (on the weekend, it's from Friday 5:00 PM to Saturday 5:00 PM, then Saturday 5:00 PM to Sunday 5:00 PM, and then Sunday 5:00 PM to Monday 7:30 AM); otherwise, we are entitled to rest time. For example, if we go out from 5:00 PM to 12:00 AM, we only have 7:30 hours of rest, and therefore we are entitled to 11 - 7.5 = 3.5 hours of compensatory rest.

    Additionally, if there is less than 1 hour of rest between two duty interventions, the time is considered worked. For example, the first intervention from 5:00 PM to 6:00 PM, and the second intervention from 6:45 PM to 7:00 PM; the worked time is considered from 5:00 PM to 7:00 PM non-stop.

    I am looking to create a calculation in which I could enter the times I was called, and it would give me the rest hours to which we are entitled.

    I tried to create a SUMIF function based on color, but I don't know how to use it...

    Thank you in advance for the help :)
    See you soon!
    0
  4. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Thank you for the file.
    It would be a minimum to include in the table how the on-call duties are recorded... And who it concerns...!

    Note that the speed of the boost depends on your feedback...!

    --
    Regards.
    The Penguin
    0
  6. Geoffrey_ Posted messages 12 Status Member
     
    Hello,
    In each cell, I was thinking of writing a location, for example, to indicate a standby intervention in Victor Hugo Street, with 1 cell corresponding to 15 minutes.

    But I am not obliged to leave it that way; if it can be done more easily another way, I'm open to ideas.

    The Excel file is for one person per week, so there's no need to complicate things with duplicates or anything; the person's name will be included in the file name.

    Best regards.
    0
  7. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Regarding the count for the right to additional rest, the explanation is not clear.
    Please provide 1 or 2 examples in your file... !

    --
    Best regards.
    The Penguin
    0
  8. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    While waiting for the response regarding the rest count, here is my proposal for entering the on-call periods.
    My proposal: https://www.cjoint.com/c/EJEqiLiqslY

    --
    Regards.
    The Penguin
    0
  9. Geoffrey_ Posted messages 12 Status Member
     
    Hello,
    Thank you for the completed file,

    Given the number of outputs generated per week, is it possible that when I click on the "enter on-call" button, I could write a text instead of calling the cells from sheet 2?

    Attached is an example of on-call duty with recovery time
    https://www.cjoint.com/c/EKciTE5rTl1

    Thank you for your help.
    0
  10. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Thank you for checking if this is suitable for entering the texts... ?
    The proposal: https://www.cjoint.com/c/EKcqmNh42XY
    Thank you for your response.

    --
    Best regards.
    The Penguin
    0
  11. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    Here is my proposal with the calculation of rest, I leave it to you to check that everything is correct according to your needs.
    The proposal: https://www.cjoint.com/c/EKdreDZz2cY

    --
    Best regards.
    The Penguin
    0
  12. Geoffrey_ Posted messages 12 Status Member
     
    Hello,
    Thank you for the help

    Small problem on Sunday, but I understood the logic

    On the other hand, if I make 2 "consecutive" outings with less than 1 hour interval, the time is not counted as outings, I will try to find a solution

    In any case, thank you for the help so far :)
    0
  13. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    It is very likely that I did not fully understand your explanations.
    You can put the example(s) that have issues in the file, mentioning the error and its correction in a text box that you insert... it's up to you.

    --
    Best regards.
    The Penguin
    0