Transfer cell values to another cell with conditions

[jsilverio21]

Hi guys!

I have been having issues with my excel sheets. I have a biometric data that I need to adjust but it wont go the way I want to. the downloaded values are as follows

Gina Salvoro 37 9/30/2019 07:52:36 102 FP/PW/RF/FACE
Gina Salvoro 37 9/30/2019 18:04:57 102 FP/PW/RF/FACE
Gina Salvoro 37 10/1/2019 07:47:54 102 FP/PW/RF/FACE
Gina Salvoro 37 10/1/2019 18:03:03 102 FP/PW/RF/FACE
Gina Salvoro 37 10/2/2019 07:34:05 102 FP/PW/RF/FACE
Gina Salvoro 37 10/2/2019 18:00:11 102 FP/PW/RF/FACE
Gina Salvoro 37 10/3/2019 07:45:34 102 FP/PW/RF/FACE
Gina Salvoro 37 10/3/2019 18:01:13 102 FP/PW/RF/FACE
Gina Salvoro 37 10/4/2019 07:40:06 102 FP/PW/RF/FACE
Gina Salvoro 37 10/4/2019 18:03:47 102 FP/PW/RF/FACE
Gina Salvoro 37 10/5/2019 07:40:32 102 FP/PW/RF/FACE
Gina Salvoro 37 10/5/2019 18:01:08 102 FP/PW/RF/FACE
Gina Salvoro 37 10/7/2019 07:39:23 102 FP/PW/RF/FACE
Gina Salvoro 37 10/7/2019 18:00:43 102 FP/PW/RF/FACE
Gina Salvoro 37 10/8/2019 07:29:57 102 FP/PW/RF/FACE
Gina Salvoro 37 10/8/2019 18:02:19 102 FP/PW/RF/FACE
Gina Salvoro 37 10/9/2019 07:30:42 102 FP/PW/RF/FACE
Gina Salvoro 37 10/9/2019 18:00:15 102 FP/PW/RF/FACE
Gina Salvoro 37 10/10/2019 07:40:19 102 FP/PW/RF/FACE
Gina Salvoro 37 10/11/2019 07:43:18 102 FP/PW/RF/FACE
Gina Salvoro 37 10/11/2019 18:01:26 102 FP/PW/RF/FACE
Gina Salvoro 37 10/12/2019 07:39:46 102 FP/PW/RF/FACE
Gina Salvoro 37 10/12/2019 18:00:18 102 FP/PW/RF/FACE
Gina Salvoro 37 10/14/2019 08:01:59 102 FP/PW/RF/FACE


as you can see there are no Logins and logouts

it just have dates and times. same dates different times
I need to move the other time to a different line so that when we submit it for payroll it will show just the in and out. something like this


Name No. Date IN OUT
Gina Salvoro 37 09/30/2019 7:52:36 18:04:57
Gina Salvoro 37 10/01/2019 7:47:54 18:03:03
Gina Salvoro 37 10/02/2019 7:34:05 18:00:11
Gina Salvoro 37 10/03/2019 7:45:34 18:01:13
Gina Salvoro 37 10/04/2019 7:40:06 18:03:47
Gina Salvoro 37 10/05/2019 7:40:32 18:01:08
Gina Salvoro 37 10/07/2019 7:39:23 18:00:43
Gina Salvoro 37 10/08/2019 7:29:57 18:02:19
Gina Salvoro 37 10/09/2019 7:30:42 18:00:15
Gina Salvoro 37 10/11/2019 7:43:18 18:01:26
Gina Salvoro 37 10/12/2019 7:39:46 18:00:18

I tried to do it using if command however I got lots of errors. here is my formula

Name No. date in
=IF(Sheet1!B2=Sheet1!B3,Sheet1!B3) =IF(Sheet1!C2=Sheet1!C3,Sheet1!C2) =IF(Sheet1!D2=Sheet1!D3,Sheet1!D2,"NO Data") =IF(Sheet1!D2=Sheet1!D3,Sheet1!D2,"NO Data")
out
=IF(Sheet1!D2=Sheet1!D3,Sheet1!E3,"NO OUT")

but it doesn't work. I get lots of errors in validation. it will show me this
Gina Salvoro 37 09/30/2019 7:52:36 18:04:57
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/01/2019 7:47:54 18:03:03
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/02/2019 7:34:05 18:00:11
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/03/2019 7:45:34 18:01:13
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/04/2019 7:40:06 18:03:47
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/05/2019 7:40:32 18:01:08
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/07/2019 7:39:23 18:00:43
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/08/2019 7:29:57 18:02:19
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/09/2019 7:30:42 18:00:15
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/11/2019 7:43:18 18:01:26
Gina Salvoro 37 NO Data NO IN NO OUT
Gina Salvoro 37 10/12/2019 7:39:46 18:00:18
Gina Salvoro 37 NO Data NO IN NO OUT
FALSE FALSE NO Data NO IN NO OUT

is there another way to do this?
I'm not very savy on excel but im willing to try anything..


thanks!

[FloMM2]

I put the info you supplied in column A.
I then used the function "=LEFT(A2,35)" which include the name, space, 37, space, date, space and time space.
I then use the function "=Right(B3,9)" which includes hh, colon, mm, colon, ss, and space.
Hope this helps.
FloMM2

[jsilverio21]

I put the info you supplied in column A.
I then used the function "=LEFT(A2,35)" which include the name, space, 37, space, date, space and time space.
I then use the function "=Right(B3,9)" which includes hh, colon, mm, colon, ss, and space.
Hope this helps.
FloMM2

Hi

thanks for the response. I tried that function and doesn't work with me for some reason all I get is the formula as text and not as formula for some reason. I attached the file here in hopes that can help in understanding my situation.

[alansidman]

Using Power Query/get and Transform which is part of Excel 2016 and located on the Data Tab of your Ribbon. Here is the Mcode

Please Login or Register  to view this content.

Your file with the calculations is attached. If you are unfamiliar with Power Query, look at the two links in my signature.

v	A	B	C	D	E
1	Name	No.	Date/Time.1	In	Out
2	Gina Salvoro	37	9/30/2019	7:52:36 AM	6:04:57 PM
3	Gina Salvoro	37	10/1/2019	7:47:54 AM	6:03:03 PM
4	Gina Salvoro	37	10/2/2019	7:34:05 AM	6:00:11 PM
5	Gina Salvoro	37	10/3/2019	7:45:34 AM	6:01:13 PM
6	Gina Salvoro	37	10/4/2019	7:40:06 AM	6:03:47 PM
7	Gina Salvoro	37	10/5/2019	7:40:32 AM	6:01:08 PM
8	Gina Salvoro	37	10/7/2019	7:39:23 AM	6:00:43 PM
9	Gina Salvoro	37	10/8/2019	7:29:57 AM	6:02:19 PM
10	Gina Salvoro	37	10/9/2019	7:30:42 AM	6:00:15 PM
11	Gina Salvoro	37	10/10/2019	7:40:19 AM	7:43:18 AM
12	Gina Salvoro	37	10/11/2019	6:01:26 PM	7:39:46 AM
13	Gina Salvoro	37	10/12/2019	6:00:18 PM	8:01:59 AM