View original (French)

How to divide an hour in sheets

Solved
dija78gjgthj Posted messages 8 Status Member -  
PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention   -
Hello,

how to divide an hour on the sheets application?
I'm calculating the duration of a night:
for example: 10:00 PM to 4:00 AM.
I managed to find the duration by calculating the difference. Now I want the formula to find half of the night, the start of the first third, and the last third. Thank you.

Configuration: Android / Chrome 67.0.3396.87

2 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     
    Hello,
    to continue your example, the start of the first third would be 10 PM and the start of the second third would be midnight?
    0
    1. dija78gjgthj Posted messages 8 Status Member
       
      Hello, yes exactly, that’s it. The first third is from 10 PM to midnight, the second third is from midnight to 2 AM, and the last third is from 2 AM to 4 AM. And the halfway point in the night is 1 AM. I want to create an automatic calculation that gives me the time of the last third (so 2 AM in our example) and also the halfway point in the night (1 AM in our example).
      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > dija78gjgthj Posted messages 8 Status Member
         
        Well, you add a fraction of the duration to the starting value: doesn't that work?
        The beginning of the last third is the start plus two-thirds of the duration,
        the middle is the start plus half of the duration.
        0
    2. dija78gjgthj Posted messages 8 Status Member
       
      Well, I just can’t do it. That’s precisely the question at hand. Do you know how to do it? If so, please give me a copy-paste of the line of code to use.
      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > dija78gjgthj Posted messages 8 Status Member
         
        how do you calculate the duration?
        you divide the duration by two, and you add that to the start time.
        0
    3. dija78gjgthj Posted messages 8 Status Member
       
      The duration is easy; you just need to do a subtraction. However, I can’t manage to divide one hour; I don’t know how it works; it doesn’t work.
      0
  2. PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention   1 511
     
    Hello

    To illustrate the response from yg_be (greetings)



    Best regards
    0
    1. PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention   1 511
       
      I correct this mistake, it's "moitié" and not "moitier"

      Best regards
      0
    2. dija78gjgthj Posted messages 8 Status Member
       
      Hello, can you send me the sheet by email please?
      I copied what you did but it doesn’t work for me.
      Did you set all the cells to time format?
      0
    3. PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention   1 511
       
      Yes, cells B1 & B2 were automatically formatted as time when I entered 22:00 & 04:00

      I don't know this spreadsheet very well yet. I only use it occasionally to test things and be able to respond on CCM

      The formulas for the sheet are in French for me after unchecking "Always use English function names" in file/settings of the spreadsheet

      I don't know if this link will be enough
      https://drive.google.com/open?id=1N_NYlJ1bk746y0oYmEf0hOOrMm3nHKZIWlsM9T2hFWc

      Best regards
      0
    4. dija78gjgthj Posted messages 8 Status Member > PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention  
       
      I don’t have the same sheet as you. In my opinion, we must not be using the same application. I’m using Sheets. Are you sure you’re using the Sheets application correctly?
      0
    5. PapyLuc51 Posted messages 4568 Registration date   Status Member Last intervention   1 511
       
      I use what was installed when I put Google Chrome on my PC. In the file properties on my Drive explorer, it is indeed listed as .gsheets - it may not be the same configuration for Android, I am on W10

      Best regards
      0