Good evening everyone, I hope you can help me.
I'm trying to divide colleagues' working times into hours in order to calculate productivity. I've managed it for the early shift and the late shift, but I can't do it for the night shift (day change). Attached is an example table. I would be very happy if you could help. Many thanks in advance.

Greetings
Charlie

Attached Files

Beispiel.xlsx (10.2 KB, 9 views)

Hi and welcome. As this an English speaking forum, please post in this language. There are lots of tools available on the Net to do so. Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Good morning, and thank you!

The easiest way would be entering start and end times as datetimes (like 13.11.2024 20:00). You can format time columns as "hh:mm", so your table looks all same, but the working time is calculated properly (unless you accidentally forget the date part of-course).

Good morning ArviLaanemets, I have also tried, then the issue is with the top line where the hours are given, there are only the hours.

I don't quite understand what you mean?
(BTW thank you for translating)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I don't understand too! And I can't look at your table, as I ma currently ill, and I don't have MS Office at my home computer.

Do you have some worktime totals in top line? When this is the case, then probably you need to format the totals cell as "[h]", or "[h]:mm".

This looks good. After entering date and time as suggested in B1, say, in the second column(C1) enter =B1+1/24 and drag right. Format as suggested.
You will have to change your references to the first row as e.g. MOD(B1;1) instead of B1 to extract time
Is that what you want to do ?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Good evening everyone,
Sorry, my table was not properly maintained, it should serve as an evaluation over a longer period of time.
Here is a new table in English.

Attached Files

Beispiel.xlsx (14.3 KB, 5 views)

I see you did not take some time to try the suggestions you already got? I suppose the attached is what you want?

Attached Files

Beispiel(4).xlsx (15.0 KB, 3 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Good morning Pecoflyer, no I would like to have a 1 in the time where the work was done (for a full hour), for the early shift and late shift I have managed it but not for the night shift not, also the 6 minutes should be 0.1 in the 14 o'clock hour, for the early shift.

Based what I have read here, I'd suggest a different design:

You need a sheet with employees table, which contains at least 1 column which indicates employee uniquely (name, ID, whatever). Define this column as dynamic range, which you can use as source of data validation list anywhere you need it;

All worktime data entry is made in single table on separate sheet, with columns like
Employee, ShiftNo, StartTime, EndTime, Date, ...
StartTime and EndTime must be datetimes, Employee and ShiftNo columns are data validation lists, and Date column is calculated as date of StartTime;

Probably you need a table where start and end times (in time format) for every shift are determined too. Like
ShiftNo, StartTime, EndTime;

Based on those tables, you can create any number of report sheets (like DailyReport, MonthlyReport, etc.) where at top you can set report conditions, and below a report table is calculated based on your data entry table, and set conditions.
I suspect to get the result you wanted in this post, the report table will be the daily one, like
Employee, 01, 02, ..., 24, Total
, with report condition the date for which you want this report. The Employees are read from employees table. The values for hours columns are calculated from data entry table as time by sumifs based on employee StartTime and EndTime at report date, and converted to numbers (1 or fraction of it) by mutipling the sumifs result with 24.

Good evening everyone, unfortunately the file from the Pecoflyer still hits the mark. I understand it, it's a bit difficult to understand, I'll try the following file. I try to display the required data with colours, the green fields are displayed correctly (not consciously during night shift). I want to display the red fields using a formula, but I can't do it.

Attached Files

Beispiel 3.xlsx (14.1 KB, 3 views)

Have a look at the table at cell AE1 of the attached.
There are some 2s in the table but that's because you have, for example, employee C working the night shift twice on the same date (rows 10 and 16 of your source data).
If this is more or less what you want then it will need tidying up and shortening because the way I've done it just happens to be what I first got to work.


(I've hidden columns F:AC, your current results.)

Attached Files

msofficeanswers52990Beispiel.xlsx (28.1 KB, 4 views)

Update
I think I was working on the wrong file; you seem to want portions of an hour too.
See attached.

Attached Files

msofficeanswers52990Beispielv2.xlsx (27.3 KB, 3 views)