+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 27

tijd registratie na 2400 uur

  1. #1
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    tijd registratie na 2400 uur

    Goedendag allemaal,
    Ik ben bezig een tijdsregistratie formulier te maken.
    Nu komt het voor dat de tijd na 2400 uur verder telt.
    Dit heb ik voor mekaar in de berekeningen van B1 tot B4 =som((B2-B1)+(B2<B1))
    Zoals jullie kunnen zien in de sheet, heb ik gekleurde cellen aangegeven met berekeningen.
    Het lukt mij niet om die bijvoorbeeld bij A7 Pauze goed te laten berekenen over de 24 uur.
    Wie heeft er een idee hoe ik dat moet oplossen?
    Het is de bedoeling dat als er in B12 pauze, laden of lossen voorkomt dat dit automatisch berekend wordt.
    Met vriendelijke groet Roland.
    knipsel excell.JPG

    Sorry also in english,

    Hello everybody
    I'am trying to make a time sheet
    Sometimes the time comes over 24:00
    In the cel B1 to B4 is the formule =SOM(B2-B1)+(B2>B1)
    I can't succeed to make tthe formula working for row 7.
    Someone got the solution?
    I want if in cel B12 pauze, loading or unloading This automatic calculated.
    Greet Roland
    Attached Files Attached Files
    Last edited by langeloo; 09-22-2018 at 09:09 AM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    Ik begrijp geen Nederlands, vertaald door Google.
    Probeer dit in I19

    =ALS(EN(B7>=$B$1;C7<=$B$2);REST(C7-B7;1);0)

    en dezelfde wijziging toepassen op de rest van de formules in I19:Q23

    Ik heb gevraagd om uw vraag te verplaatsen naar het niet-Engelse gedeelte van het forum waar anderen die uw taal begrijpen, mogelijk beter kunnen helpen.

  3. #3
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    Now it is calculation cels I19 L19 and O19 the same.
    Last edited by langeloo; 09-22-2018 at 09:27 AM.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    Please you re-post your sample file, but this time fill in some data in B1:B4 and B7:C9 then replace the formulas that are wrong with the results that you expect.

  5. #5
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    i changed the file in row 19. didn't change row 20 and 21.
    20 and 21 count it in the right way, but if i made a time of 23:45 (in row 19) it count in row 12 the time and not the time B2-B1 -row 19
    Or i didn't understand yot. At least thx for the thinking.

    hmm ican't upload the sheet anymore???
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    Sorry, something that I missed before.

    In I19

    =ALS(EN(B7>=$B$1;(C7+(C7<B7))<=($B$2+($B$2<$B$1)));REST(C7-B7;1);0)

    Do rows 20 and 21 as well, if you don't then B2 before midnight and B3 after will mean row 20 is wrong, B3 before midnight and B4 after will mean row 21 is wrong.

    Hope that makes sense.

    Also, in B12

    =REST(B2-B1;1)-SOM(I19:Q19)

    Your formula looks correct, this is just a little shorter answers will be the same.

  7. #7
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    Yes now it's working if B7=23:45 and C7=0:30. than its overnight counting

    If i fill in B7=0:30 and C7=0:45. it won't count.
    The rest (function is working as well.
    It's also possible that in the time between B2 and B3 are two times pauze, or 1 pauze and 1 unloading.

    Or possible that 2 times unloading and loading between B2 and B3
    Knipselexcell 2.JPG

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    Oops, I missed that possibility.

    I'll have another look at it in the morning to correct that and see if there are any other possible failure points.

    Do you need the itemised times in I19:Q21, or just the final totals in B12:B18?
    Last edited by jason.b75; 09-22-2018 at 06:45 PM.

  9. #9
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    I send the Original file i created as first. I tried to calculate in from this form. But in the first one i tried i made the colored calculation to make it easyer.
    pff unable to send file
    Attached Files Attached Files
    Last edited by langeloo; 09-22-2018 at 06:58 PM.

  10. #10
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    the file is there. i made comment in the file. The colored I19:q21. did i use to make the formula simpel. I tried to make the formula in the invulforum sheet direct in B41. I couldn't find the right formula. So that's why i made it with the colored boxes in the first sheet.
    The colored boxes could be hided.

    Yes the totals are okay. B12:B14 total -row 7,8,9 and B15:b17 total row 7,8,9 and then total invoice.

    You can see that as well in the original sheet.
    There would it be B41:b43 time - row 35, 36, 37 and 38.
    B44:B47 total of row 35, 36, 37 and 38.

    The invul form is send to customers. The don't need to see the formulas or calculation. The sheet is secured and the formulas (or needed cels hidden when ready.

    Thx
    Last edited by langeloo; 09-22-2018 at 07:29 PM.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    I think I have it right this time, this is based on the Invul file in your latest post.

    C41 =REST(I30-I29;1)*24
    C42 =REST(I32-I31;1)*24
    C43 =REST(I31-I30;1)*24-SOM(C44:C46)
    C44 fill down to C47 =(REST(D35-C35;1)+REST(F35-E35;1)+REST(H35-G35;1))*24

    Try that with various times and see how well it works. I think it will be wrong if (C32+I32)-(C29+I29) is more than 24 hours. If that is possible then I think that you will need to change your method of data entry to include date and time for every record.

  12. #12
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    It works but i think we misunderstood each other. I want to calculate the time in row 35. 36, 37 and 38. if the time in C35 and D35 is between I29 and I30. it has to calculate C41=REST(I30-I29;1)-(D35-C35;1). But is the time in C7 and D7 between I30 and I31 then the formula has to be C43=REST(I31-I30;1)-(D35-C35;1). And on. Is the time between I31 and I32 Also the same.
    what i want is that the formula in C41 has to detect if the time (in row 35:38 (C:D) or(E:F) or(G:F) is in between (I29 and I30) Or (I30 and I31) or (I31 and I32). Then calculate. If i make pauze (row 38) in between I30 and I31 it has to calculate c43 total time -pauze or loading or unloading. C44:C47 are just the total in the row 35:38.
    That why i made the first example with al the colors. Every cel in the colors were a formula to calculate every cel in B7:H9.
    I think its complicated, but in de first form it worked except the problem after midnight.

    In row C29:C32 is the date filled in. When the time in I29:I32 goes to the nest day it automatic calculate C30:C32.
    Hmm that something to think about. But of course no date is filled in C35:H38.
    At least thank you for thinking.
    Is it an idea to made the cells in C35:H38 filled with date and time, but yeah then its a lot of work then.
    My work is pilot exceptional transport. This sheet is on my phone and fill it when i am working.

    I29:I30 is driving to loading site. I30:I31 is driving the transport, I31:I32 is driving home.

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    Sorry, I missed a bit on the formula for C41

    =REST(I30-I29;1)*24-C44

    I think that is what you need based on your latest post, you might need to make similar changes to the formulas in C42 and C43.

    Dates in C35:H38 will not be needed as long as you don't go over 24 hours. More than 24 hours will not work without dates.

  14. #14
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    2016
    Posts
    21

    Re: tijd registratie na 2400 uur

    i thought i made a reply but it went wrong.
    I made a new form now with a cell notation of dd-mm-jjjj uu:mm now everything is working.
    Still got a few questions. When i change the cell in uu:mm again the date is also seen in the cell.
    Is it possible that the customer only sees the time? Ithink the form is a bit cluttered when you display
    dd-mm-jjjj uu:mm or dd-mm-jj uu:mm Or is it possible that you can change the look in, by example dd-mm-jj UU-MM
    So you can see a difference between date and time.

    And i made in the form a date in B2:B5 with the formule =ALS((B3<B2);B2+1;B1) but it won't work anymaore. But i can delete those cells.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,706

    Re: tijd registratie na 2400 uur

    If you format a cell taht contains date and time as date only then the date will be hidden.

    If you select the cell the date should still show in the formula bar, but the cell should only show the time.

    If you try to change the time then the date will be visible while you are editing, but as soon as you press enter, it will revert back to time only.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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