Hours calculation

[Corall]

Hi,

Need some help:

[TABLE="width: 548"]
<colgroup><col width="64" style="width: 48pt;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 3953;" span="2"> <col width="64" style="width: 48pt;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;"> <col width="82" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2901;"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2531;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 111, bgcolor: transparent"]B[/TD]
[TD="width: 111, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 71, bgcolor: transparent"]E[/TD]
[TD="width: 82, bgcolor: transparent"]F[/TD]
[TD="width: 94, bgcolor: transparent"]G[/TD]
[TD="width: 71, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Data Start[/TD]
[TD="bgcolor: transparent"]Data End[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Name[/TD]
[TD="bgcolor: transparent"]DATA[/TD]
[TD="bgcolor: transparent"]Day hour[/TD]
[TD="bgcolor: transparent"]Night hour[/TD]
[TD="bgcolor: transparent"]TOTAL[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]1.sept. 2:00[/TD]
[TD="bgcolor: transparent"]1.sept. 5:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]1-sept.[/TD]
[TD="bgcolor: transparent"]5+2[/TD]
[TD="bgcolor: transparent"]3+2[/TD]
[TD="bgcolor: transparent"]12[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1.sept. 11:00[/TD]
[TD="bgcolor: transparent"]1.sept. 16:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2-sept.[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1.sept. 20:00[/TD]
[TD="bgcolor: transparent"]2.sept. 6:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2.sept. 13:00[/TD]
[TD="bgcolor: transparent"]2.sept. 17:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]1-sept.[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]1.sept. 7:00[/TD]
[TD="bgcolor: transparent"]1.sept. 13:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name 3[/TD]
[TD="bgcolor: transparent"]1.sept. 7:00[/TD]
[TD="bgcolor: transparent"]1.sept. 11:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Nume 3[/TD]
[TD="bgcolor: transparent"]1-sept.[/TD]
[TD="bgcolor: transparent"]4+3+1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1.sept. 16:00[/TD]
[TD="bgcolor: transparent"]1.sept. 19:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2-sept.[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1.sept. 21:00[/TD]
[TD="bgcolor: transparent"]2.sept. 2:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Night hour 22:06[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

I want to calculate the hours worked by each employee, in 24 hours (from 0 to 24 hours in the same day).
The night hours will be calculated, if they are in the between 22:06.

Calculation example:

Name 1
worked on September 1, from 02:00 to 05:00, these hours (3 hours) will be passed at night because they are between 22:06. Also at night hours will be passed at 22:24 because the remaining hours will be passed on September 2, so it will be 3 + 2 = 5 hours at night.
The daylight hours are 5 + 2 = 7 hours a day.
For September 2 will pass 4 hours a day and 6 hours a night.
The same will be calculated for Name 2 .... Name 10


Thank you.

 

[Corall]

Hi,

If one did not understand what appeared in that table, I can clarify:
In cell B2 (actually in columns B and C there are dates and times) is given 1.09.2019 02:00:00 (September 1, 2019 at 02:00:00) and formatted appeared 1.sept. 2:00.
I would like to help me, with a formula, I can calculate, the working hours in the night (between 22:00 and 6:00), and the working hours of the day (between 6:00 and 22:00).
In cell G2 I would like to see 7 (not 5 + 2, I showed in the table, to understand where those numbers come from). Same for the G7 cell.

Thanks.

 

[Corall]

Nobody can help?

 

[Corall]

I can't put a pictures...

 

[Corall]

Hi,

I managed to calculate the total number of hours for an employee (for the whole period).
I am unable to calculate the number of hours worked at night nor the hours worked during the day.
Nobody can help me?

 

[Corall]

bump

 

[Corall]

Thank you for your kindness and help.

I can not believe that no one knows a solution to the problem posted.
I think you didn't have the time / kindness to help me.
I wish you good health.

 

[lezawang]

Hi
People here are basically for one reason, is to help. I have been getting all kind of help and without this site, I would have still not learn the basic of excel. If no one answer (happened to me) is because the question was not clear or I did not provide clean and easy to understand excel sheet. I recommend to upload your sheet to google drive and share it (this was an advice from one of MVP here). I wanted to help because I am very interested in Time and Date functions but could not understand what you want to do.