# Microsoft Office Application Help - Excel Help forum > Non English Excel >  > [SOLVED] tijd registratie na 2400 uur

## langeloo

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

----------


## jason.b75

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.

----------


## langeloo

Now it is calculation cels I19 L19 and O19 the same.

----------


## jason.b75

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.

----------


## langeloo

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

----------


## jason.b75

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.

----------


## langeloo

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

----------


## jason.b75

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?

----------


## langeloo

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

----------


## langeloo

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

----------


## jason.b75

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.

----------


## langeloo

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.

----------


## jason.b75

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.

----------


## langeloo

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.

----------


## jason.b75

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.

----------


## langeloo

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.

----------


## jason.b75

Change t to whatever you want the customer to see.

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

----------


## langeloo

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.

----------


## jason.b75

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!

----------


## langeloo

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.

----------


## jason.b75

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.

----------


## langeloo

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.

----------


## jason.b75

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.

----------


## langeloo

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

----------


## langeloo

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.

----------


## langeloo

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.

----------


## langeloo

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

----------

