SOLVED

Changing Date For Night Shift Employees

%3CLINGO-SUB%20id%3D%22lingo-sub-3072163%22%20slang%3D%22en-US%22%3EChanging%20Date%20For%20Night%20Shift%20Employees%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3072163%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%20I'm%20having%20a%20problem%20placing%20Employee%20logs%20in%20a%20single%20row%20due%20to%20changing%20dates%20the%20next%20day%20in%20their%20logs.%20I%20want%20to%20know%20if%20there's%20a%20formula%20I%20can%20use%20to%20set%20their%20login%20according%20to%20the%20Day%20they%20punched%20%22In%22.%20Example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ronaldonaire_0-1643169660005.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342453i5FCD9EB776D26055%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ronaldonaire_0-1643169660005.png%22%20alt%3D%22ronaldonaire_0-1643169660005.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ronaldonaire_1-1643169744840.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342454iCA87E7916E224FF5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ronaldonaire_1-1643169744840.png%22%20alt%3D%22ronaldonaire_1-1643169744840.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Guys%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3072163%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi Guys, I'm having a problem placing Employee logs in a single row due to changing dates the next day in their logs. I want to know if there's a formula I can use to set their login according to the Day they punched "In". Example:

 

ronaldonaire_0-1643169660005.pngronaldonaire_1-1643169744840.png

 

Thanks Guys

6 Replies
can you send small portion of file so we can try something

Hi! Sorry I can't seem to find the attach file here but here's the sample file:  https://drive.google.com/drive/folders/14yz9gNmUlOwQbq4h43nRGE1nKq_YY1iz?usp=sharing

best response confirmed by Hans Vogelaar (MVP)
Solution

@ronaldonaire Just added one column to the time-log that determines the Clock IN time for each entry, based on the Break Type. Then, the pivot table can be like I inserted in the Final Data tab, from row 16.

@Riny_van_Eekelen Thank you for your answer I have tried it and it works! Can you tell me how did you split your created Clock IN row to be able to show minutes, hours and Days? I can't recreate what you did here.  

 

ronaldonaire_0-1643183490169.png

 

@ronaldonaire When I dragged the Clock IN field into the rows area, it automatically created fields for days, hours and minutes. Then I reformatted the pivot table and unchecked the Clock IN, Minutes and Hours fields. 

It worked! Thank you again for your answer, it helped me a lot!