Clock hours matrix in google sheet

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

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?

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

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?

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.

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

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

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.

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.

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.

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

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

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

i will have a look into it for you.

i have sent request for access.

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

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