+ Reply to Thread
Results 1 to 27 of 27

tijd registratie na 2400 uur

  1. #1
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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
    office 365
    Posts
    60

    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
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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.

  16. #16
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Hmm i tried to change the cell. I did right mouseclick then cell and change it to date 14-03-2018. Or do i change it into dd-mm-jjjj?
    Or do you mean the time?
    I tried different options in the change but if i make it time and i fill in (date time) it display date and time in a cell that is ment for only time.
    Or do i haven to delete data in the cells before i can edit the charaterics?
    In the form i'am gonna hide the coulored cells.

  17. #17
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: tijd registratie na 2400 uur

    Change t to whatever you want the customer to see.

    If you only want them to see the time them UU:MM

  18. #18
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Yeah okay, but i have 3 to 5 forms a day, so i don't wanna change every form when i'am home. I fill the form on my phone, when i'am home i print the form and book the hours, then i print the customer and tour details on the form (this is automaticly done in my accountacy program) and send the form with the invoice to the customer by email. I have the form secured so i can only fill in the dates i need. The customer gets a scan of the form. So change afterwards is no option.

    Thats why my question was if it's possible to change the format you see in the form. Like dd-mm-jj uu:mm or something, mayby it looks a little bit less cloutered otherwise it's mayby a lot of information
    I have to design a whole new form so i can make it look how i want.

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: tijd registratie na 2400 uur

    You don't have to change it again after, just format it as uu:mm

    You can enter date and time, but only the time will show. The date will still be there but you will not see it.

    If you want to format as dd-mm-jj uu:mm then you will have to change every one individually!

  20. #20
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Hmm what do i wrong?
    I change a cell when it's filled in uu:mm then you see the time and when you click on it the date and time is shown in the topbar.If i clean the cell of date and fill it new as 22:00 it's show 22:00, if i fill in 14-03 22:00 then it'shown in the cell. I tried to change the format in a clean cell as well.

  21. #21
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: tijd registratie na 2400 uur

    Try it with conditional formatting instead.

    Use a formula to determine which cells to format, in the formula box enter

    =WAAR

    Then set the fomat as uu:mm on the 'number' tab of the formatting box.

  22. #22
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Hmm or i don't understand you or it doesn't work. The cells where i just wanna see uu:mm don't have a formula. That are the cells that i fill in with time of start stop or loading Cells C2:C5 and cells B7:E9 in the form named testnieuw i attached as last form.

  23. #23
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: tijd registratie na 2400 uur

    Condtional formatting is something in the background, it is typically used to change the colour of a cell to highlight things that meet specific conditions, but it has other uses as well.

    My translation tool doesn't work for menu items, I've given the exact path to follow in english, hopefully it will be enough for you to follow.

    Select the cells(s) where you want to enter the date and time but only want to see the time.

    On the excel ribbon, go tot he 'Home' tab, then look for 'Conditional formatting' > 'New rule' > 'Use a formula to determine which cells to format'.

    Enter =WAAR in the formula box on the popup, then click the 'Format' button.

    Go to the 'Number' tab, then set the appropriate format here, uu:mm

    Click the 'OK' buttons on all of the boxes.

  24. #24
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Okay wonderfull it works. thank you. I preciate your help, it wasn't simple.
    Is this now only in this form? or in options for excell?
    Hmm after testing still one thing. Wenn I fill the cell B2 with a date 14-03-18 and C2 is 14-03-18 23:00. if C3 filled with 15-3-18 01:00. i would like that cells b3:B5 are automaticly filled with the next date. Of course if C4 is overnight the date change in B4.

    In the old form i used the formula =ALS((C3<C2);B2+1;B2) but that doesn't work anymore

  25. #25
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    I figured it out. Because i use the date and time in cel C3:C5, shown as time. Thanx for the solution.
    Did the same thing in De cells B2:B5 for the date. The formule is now in B2 (=C2) it display the date in B2.
    This also goes for de rest of the cells.
    Still wanna thank you for your help.
    If your in holland email me and we're gonna drink some cognac or whisky.
    Last edited by langeloo; 09-26-2018 at 03:34 PM.

  26. #26
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Hi Jason hmm i found't something that i wanna change but don't know how.
    I have got the fromula =SOM(C3-C2;1)-SOM(F13:K13) it display a time of 1 h and 30 m. I wanna change this in 1.5 H.
    Tried to multiply the formula with 24 but that didn't work. Looked up on internet and found something as (integer) but i still can't figure it out.

    Posible of course this is realy simple. Changed the cell as you discribed conditional formating.

  27. #27
    Registered User
    Join Date
    09-05-2018
    Location
    harderwijk nederland
    MS-Off Ver
    office 365
    Posts
    60

    Re: tijd registratie na 2400 uur

    Sorry i found it. Nee to make the formule in another cell. at least thanx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 2400 clock help
    By m3d1c123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2018, 02:39 PM
  2. how do I allow for 24:00 (2400) timestamp in my code ??
    By Dj Duck in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-27-2016, 08:24 PM
  3. excel automatische tijd door op cel te klikken
    By Stroke19 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2013, 10:05 AM

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