# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Clock hours matrix in google sheet

## FvdF

Dear forum users,

I have a googlesheet where users have to fill in the hours they have worked. This works as it should. But this also needs to include bonuses.
So working time between 24:00-06:00 is +20%, working time between 18:00 and 24:00 is +15%.

Can this be done in my sheet? I have little knowledge of Google spreadsheet so any help is welcome..

I have two sheets, one "DATA" and the other "INFO" for to fill in the times and the break.

The attached file is originally a google sheet file but due to downloading it became an xlsx file.

Kind regards,
FvdF

----------


## janmorris

as the check boxes can not be propagated from xlsx to g-sheets, and so that we do not need to recreate the sheet from scratch, are you able to attach a txt file with a link to a copy of the google sheets file?

----------


## FvdF

Dear Janmorris,

I have attached a txt document and hope you can continue with it. I would be very happy if you can make what I want. 
I have on tab "clochmatrix" the right hours so the bonus should be calculated.

With Regards,
FvdF

----------


## janmorris

ive just had a look at the sheet.

there is a checkbox for breaks, but after comparing the sample data against the hours matrix, it appears that the current format is deficient.

for example, if someone works for several hours, and those hours span across various timeframes, then it will not be possible to properly calculate the bonus as it is unknown during which block the break was actually taken.


Hours Matrix.png


can you provide any more information?

----------


## FvdF

The checkbox is only when you have had a break. I can also put this somewhere else if that is better.

What more information do you need? I want to give as much information as I can as long as I know what?

Here are the real working hours for each shift.

Grzt.

----------


## janmorris

the new information creates more questions than it answers.

Nachtdienst "vr" has no end time, is there an end time?

is the "Pauze" always at the same time each day for each working shift, or can the time vary or be random time? is the deduction for Pauze only 30 minutes, or is deduction dependent on which block the break was taken in?

in the data image 2, on 23-10-2021 start at 2:15, which does not match the list in the first image (maybe should be 2:00?), and the end time and therefore the total hours do not match any of the image 1 data... does this mean people can have any start and finish times, and still get rewarded by working in blocks that have higher bonuses? or is this based on management approval and when there is no approval then the person simply gets the bonus according to their schedule shift?

----------


## FvdF

Dear forum users,

I have given some unclear information, sorry for that. The current "clock matrix" sheet is for the on-call workers who have to fill in their hours worked here. I think this works well.
In our accounting department, these hours are then paid out on the basis of the hours matrix. This is calculated manually.
I would like to arrange this automatically with a google sheet. But I can't do that, I don't have enough knowledge.
Therefore my call to you to look at my current "clockmatrix" sheet and to see if this can be adjusted to what I want?

If something else must be made for this I find that good too. But is it possible to make this with google sheets?

The end time for vr: 20:00hr I have to find out.
The breaks are always in the middle of the working hours.

----------


## janmorris

i may have a partial solution for you. the solution i will detail here does not yet have accommodation for the Pauze/Break, however the current formulas and structure should lead you to a resolution for that..

the solution offered here does however provide a matrix of working hours for each block, which you can then add more columns to give the totals as you like.

the basic methodology was :

identify which half hour periods were worked, starting from midnight, using this formula:



```
Please Login or Register  to view this content.
```


Screen Shot 2021-10-26 at 1.42.11 am.png

next was to get a total based on the day according to the time blocks in your matrix, using formulas such as this:



```
Please Login or Register  to view this content.
```


and this



```
Please Login or Register  to view this content.
```


Screen Shot 2021-10-26 at 1.44.27 am.png

Then finally is to calculate the bonus, again according to the weekday and the percentage in the time blocks in your matrix, such as this:



```
Please Login or Register  to view this content.
```


and this:



```
Please Login or Register  to view this content.
```


Screen Shot 2021-10-26 at 1.46.28 am.png


as this does not use checkboxes, it is possible to be used also in Excel.

simply download the attached file, then upload to google drive, open it then save as google sheets file  :Smilie:

----------


## FvdF

Dear Janmorris,

I am very happy that you were able to make something. Only my knowledge of google sheets and formulas is so bad that I can't figure it out with your explanation.
Is it possible for you to make it even clearer for me, so that I can build on it or do you want to make it even more complete?
It's not that I don't want to, I just don't have the knowledge and have to find these things on the internet. 
With the help of well-intentioned people like you, I often manage to find it, but this one is very complex.

I've been working on it for a while but just can't figure it out.

With Regards,
FvdF

----------


## janmorris

Im sorry FvdF, I am still confused how Pauze/Break should be handled.

For example, if someone works 6 hours on Saturday, from 11am to 5pm:
will they have a break? yes/no/maybe?
and if they have a break, is it deducted during the 50% block or 100% block?
or is 15 minutes taken from each?
or is all this wrong thinking and Pauze/Breaks only taken 9:30-10:00, and 17:30-18:00 according to the Work Times list?
and if that is the case, are breaks always taken, or maybe taken?

It would be most helpful if you could explain this more so as to clear up my confusion, or alternatively provide sufficient sample data (and manually calculated results with notes) to make it clear so that formulas can be made to automatically calculate the intended results.

----------


## FvdF

Janmorris,

I want to give you all the information you need. So ask me and I will find out.

What I know now is that the break is simply deducted from the total hours worked. So it has no time notation.

Grtz.

----------


## janmorris

1.




> What I know now is that the break is simply deducted from the total hours worked. So it has no time notation.



so there is only deduction from the total hours, but no deduction of any bonus, therefore staff are paid the bonus while having a break?

2.
if you could also find out if breaks are optional or compulsory, and if they are only according to the the schedule of work shifts list, or perhaps there is some missing data?
for example, it seems peculiar that nightshift VR has no ending time, so if this is evidence of missing data then perhaps there is other missing data for other shifts on other days.

----------


## FvdF

The working time from Friday 22:00 is until Saturday 02:00. Each shift has a different total of hours per week, but over three weeks 
they have worked 36 hours per week. The half-hour break is always calculated as normal time.

The break is required but may not be taken at certain times. This depends on the amount of work at the time. Therefore the check mark I made for this  :Smilie:

----------


## janmorris

okies, based on that only the standard hours are deducted, and the bonus is still paid without any change, i simply added in a checkbox column and then a total column.

please visit the link to make a copy:

https://docs.google.com/spreadsheets...ALZlaSoDc/copy

----------


## FvdF

Hello Janmorris

I have a copy of this now and will test it out next week. 

In any case, I would like to thank you for your help and will let you know if we can work with this properly.

Regards,
FvdF

----------


## FvdF

Hello janmorris,

I have looked at your google sheet and it is amazing, it works well. Thanks a lot for this.
However, I still have some problems. Your "Hours Matrix" is for our administration colleagues. I have an "A" document that is for the on-call worker. They have to fill in the date and hours (without : this is easier) and I want to use this code to fill in these data in the Hours Matrix document (=IMPORTRANGE("12hu4r6QSoqwEfdkXFQsbb6wMs2_FOMPKFNHKlJrOUUw";"INFO!A5:E")

Both sheets work fine on their own, but when importing into Hours Matrix it goes wrong. How do I get these columns form TEST(A) A, B, C and D into the Hours Matrix sheet?

https://docs.google.com/spreadsheets...it?usp=sharing
https://docs.google.com/spreadsheets...it?usp=sharing

Regards,
FvdF

----------


## janmorris

i will have a look into it for you.

i have sent request for access.

----------


## janmorris

here is a script that will work, with one condition:

Time MUST be entered as Time including the colon (*eg. 12:34*), time can not be entered on the info sheet as plain numbers 1234

---

EDIT:
An alternative that can allow you to use time as a 4 digit number is to add another two columns into the Hours Matrix sheet, and then do the same formula "conversion" as is done on the DATA sheet.. then you will need to modify all the formulas in from column E and onwards to look at the converted time instead of the numbers time.
i hope this makes sense

---

i have given the constants names that will be identifiable, so you can see what is going on.

the "Clear Data" function is optional extra just to clear out the form if you want to see it plain, it is also built into the import function as an "all in one" solution to clear and then import data




```
Please Login or Register  to view this content.
```


i have made the necessary adjustments to the Hours Matrix, and entered the Apps Script. it is ready for you to run and try it out  :Smilie:

----------


## FvdF

Hello Janmorris,

I am very surprised, it seems that everything is going to work as it should. 
I still have one problem, in sheet "C" I have indicated in yellow what is not working now. If I enter 2400 hr or 0000 hr in column "B" in tab "INFO" then it does not work (any more). So I want to continue to fill in the time here without :

In your HoursMatrix sheet this does work well.

Can this still be solved?


https://docs.google.com/spreadsheets...it?usp=sharing

----------


## janmorris

it looks like it is either from the sheets/cells being locked (some types of formulas do not respond well when sheets are locked), or some kind of issue with the IF-MOD formula in DATA!E.

although i cant pinpoint what exactly is causing the issue on your spreadsheet, i have recreated it on the Hourly Matrix spreadsheet, and it appears to be working even when the sheets are locked.

please see the new Hourly Matrix v2 for method that works with 0000 and 2400 for the start OR end times... note that you will get a result of 0 if using 0000 AND 2400 together for the start AND end time... but who works for 24 hours straight anyway, right?

https://docs.google.com/spreadsheets...fu0XLlmmA/copy

----------

