Total worker duration

[Heera]

Hello Team,

Can anyone please simplify the below requirement?

I am looking for a total worker working hours from the time of start of the shift to the end of the shift.

Please add the formulae or create a template and share with me. Sample data are shown below :

[TABLE="width: 456"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]User Name[/TD]
[TD] Login Time[/TD]
[TD] Logout Time[/TD]
[/TR]
[TR]
[TD]Bayleigh Osmer[/TD]
[TD]31.1.2018 5:57:06 PM[/TD]
[TD]31.1.2018 9:40:10 PM[/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]31.1.2018 4:04:12 PM[/TD]
[TD]31.1.2018 8:14:23 PM[/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]1.2.2018 11:58:59 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]1.2.2018 12:39:33 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]1.2.2018 12:49:08 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]1.2.2018 12:59:30 PM[/TD]
[TD]1.2.2018 1:10:05 PM[/TD]
[/TR]
[TR]
[TD]Adam Davis[/TD]
[TD]1.2.2018 1:14:58 PM[/TD]
[TD]1.2.2018 1:20:34 PM[/TD]
[/TR]
[TR]
[TD]Brittany Tharps[/TD]
[TD]31.1.2018 4:44:50 PM[/TD]
[TD]31.1.2018 6:22:59 PM[/TD]
[/TR]
[TR]
[TD]Brittany Tharps[/TD]
[TD]31.1.2018 6:56:06 PM[/TD]
[TD]31.1.2018 7:37:03 PM[/TD]
[/TR]
[TR]
[TD]Brittany Tharps[/TD]
[TD]31.1.2018 8:10:37 PM[/TD]
[TD]31.1.2018 8:51:08 PM[/TD]
[/TR]
[TR]
[TD]Brittany Tharps[/TD]
[TD]31.1.2018 9:06:53 PM[/TD]
[TD]31.1.2018 11:27:16 PM[/TD]
[/TR]
</tbody>[/TABLE]

I have total 200 workers.

Regards,
Heera

 

[Dave Patton]

What date format does your system use?
What are your regional settings for dates?

Is the data shown real dates and times or Text

N.B. The solution works with Regional setting of dd-mmm-yy

Excel 2010
	A	B	C	D	E
2	Bayleigh Osmer	31.1.2018 5:57:06 PM	31.1.2018 9:40:10 PM	03:43:04	3.71778
3	Bayleigh Osmer	31-Jan-18 17:57:06	31-Jan-18 21:40:10	03:43:04	3.71778
4
1a

Cell Formulas
Range	Formula
D2	=SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-")
D3	=C3-B3
E2	=(SUBSTITUTE(C2,".","-")-SUBSTITUTE(B2,".","-"))*24
E3	=(C3-B3)*24

 

[Heera]

Hi Dave,

Thank you for your reply. When I am pasting D2 formulae it is giving me #Value ! error. Can you please help me with the other way?

Is it possible for me to send the excel file?

Regards,
Heera

 

[Dave Patton]

You did not answer the questions in post #2 .
date format does your system use?
What are your regional settings for dates?
Is the data shown real dates and times or Text

You can post a concise example of your data; see help information on the site and suggested add-ins.

 

[Heera]

Hi Dave,

Here is my date system format :

Short date: 2/5/2018
Long date: s Monday, February 05, 2018
Short time : 9:43 PM

Time zone : (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi

I am getting this data from the application. After exporting to excel, the data and time format is showing in general format. I tried changing to another format which is available in date option but it is not changing.

Please help.

Regards,
Heera

 

[Dave Patton]

You advised that your system's settings for dates are mm/dd/yy and the data is Text.
Is mm/dd/yy the date format that you use in your Country?

Please answer the following questions.
1. Can the "application" export the data as True dates and times?

2. What are the expected results with the example that your posted and how did you calculate the results?

 

[Dave Patton]

try
=TIMEVALUE(TRIM(RIGHT(C2,11)))-TIMEVALUE(TRIM(RIGHT(B2,11)))

 

[Heera]

Hi Dave,

Thanks for your reply. How can we fix the below dates? When the date is changing I am getting #### value in the cell after inserting the above formulae.

[TABLE="width: 436"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]User Name[/TD]
[TD] Login Time[/TD]
[TD] Logout Time[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Precious Kelly[/TD]
[TD]31.1.2018 10:19:08 PM[/TD]
[TD]1.2.2018 6:42:20 AM[/TD]
[TD="align: center"]########[/TD]
[/TR]
[TR]
[TD]Justin Taylor[/TD]
[TD]31.1.2018 10:51:39 PM[/TD]
[TD]1.2.2018 1:19:15 AM[/TD]
[TD="align: center"]########[/TD]
[/TR]
[TR]
[TD]Patricia Chandler[/TD]
[TD]31.1.2018 4:26:08 PM[/TD]
[TD]1.2.2018 12:58:07 AM[/TD]
[TD="align: center"]########[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

You did not answer the questions in Post # 6 !
What progress have you made with the suggestions?
You did not indicate the times straddled midnight.

Please post a clear example of your challenge.
Your example should include expected results.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
18	Precious Kelly	31-Jan-18 22:19:08	01-Feb-18 6:42:20			8.39
19
20	User Name	Login Time	Logout Time			Decimal
21	Precious Kelly	31.1.2018 10:19:08 PM	1.2.2018 6:42:20 AM	22:19:08	06:42:20	8.39
22	Justin Taylor	31.1.2018 10:51:39 PM	1.2.2018 1:19:15 AM	22:51:39	01:19:15	2.46
23	Patricia Chandler	31.1.2018 4:26:08 PM	1.2.2018 12:58:07 AM	16:26:08	00:58:07	8.53
24
25
1a

Cell Formulas
Range	Formula
F18	=(C18-B18)*24
F21	=IF(D21>E21,(1-D21+E21)*24,(E21-D21)*24)
F22	=IF(D22>E22,(1-D22+E22)*24,(E22-D22)*24)
F23	=IF(D23>E23,(1-D23+E23)*24,(E23-D23)*24)
D21	=TIMEVALUE(TRIM(RIGHT(B21,11)))
D22	=TIMEVALUE(TRIM(RIGHT(B22,11)))
D23	=TIMEVALUE(TRIM(RIGHT(B23,11)))
E21	=TIMEVALUE(TRIM(RIGHT(C21,11)))
E22	=TIMEVALUE(TRIM(RIGHT(C22,11)))
E23	=TIMEVALUE(TRIM(RIGHT(C23,11)))