+ Reply to Thread
Results 1 to 11 of 11

Split one colunm in severals columns in accordance with criterias

  1. #1
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Split one colunm in severals columns in accordance with criterias

    Hello everyone,

    I am facing an issue to organize in order the checking time of each employee, to explain you our HR extract to excel each month the attendance report from a software and she place all excel files in a specific folder, with Query table I managed to combine all files that contain the folder, sorted out the date from A to Z, spited the column containing all working check time of the day in several columns (one column for each check time) but the issue that I have is that sometimes the employee forget to check at morning, afternoon or night (no rule on it will depend of each employee), that mean the working time order for each employee will be at different position.

    For exemple :
    Employee 1 : 07:30 11:58 13:31 17:28 18:01 21:00 --> So this is a normal situation, we have the working time in/out for morning, afternoon and night.
    Employee 2 : 11:56 13:31 17:28 18:01 21:00 --> In this case the employee forget to check in at the morning, so the time order is not correct.

    With this kind of record when I split out all time record in several columns I will get for exemple for the afternoon check in 13:31 for employee 1 (that is correct) but for the employee 2, 17:28 instead of 13:31.

    I don t know how to give a logics to excel to split correctly the time in each column. I will be really glad if someone have a way to solve it.

    Thank you
    Geoffrey

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Split one colunm in severals columns in accordance with criterias

    First, try to upload a sample worksheet (On top of the page you will see a yellow banner guide you how to attach)
    Second, You must assign the time into a tie set, i.e, if time after 10:00 AM, it should be "time out" of morning shift...
    Quang PT

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    Hello Bebo,

    thanks for your answer,you can find attached the file to illustrate my issue.

    Geoffrey
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Split one colunm in severals columns in accordance with criterias

    What do you have those what you want it to be?
    Could you add more "before" and "after" information?

  5. #5
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    In fact I need to have 6 columns : morning check in time / morning check out time / afternoon check in time / afternoon check out time / night check in time / night check out time. In each column I should have the correct time for each employee. I hope it s clear enough, let me know if not.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Split one colunm in severals columns in accordance with criterias

    Sorry, still didnot.
    Again, could you add some mock up results?

  7. #7
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    Sorry I don t understand your question.

    This exemple illustrate well my issue :
    Employee 1 time record : 07:30 11:58 13:31 17:28 18:01 21:00 --> All theses time record come from a software and are in an unique cellule, one excel line for one day. So this is a normal situation, we have the working time in/out for morning, afternoon and night.
    Employee 2 time record: 11:56 13:31 17:28 18:01 21:00 --> All theses time record come from a software and are in an unique cellule, one excel line for one day. In this case the employee forget to check in at the morning, so the time order is not correct.

    I upload an exemple of source excel file (imported from software) and you will see that for a define position we don t have always the correct time because the employee forgot to check in for exemple.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    Attached a manual simulation of what I need.
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Split one colunm in severals columns in accordance with criterias

    Please try at H4

    =IFERROR(LOOKUP(2,1/(ABS(MID($E5,{1,7,13,19},6)-H$2)<MIN(I$2-H$2,H$2-N(G$2))/2),--MID($E5,{1,7,13,19},6)),"")

    or fixed different time with Check in/out to 15 minutes

    =IFERROR(LOOKUP(2,1/(ABS(MID($E5,{1,7,13,19},6)-H$2)<TIME(0,15,0)),--MID($E5,{1,7,13,19},6)),"")
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    Hello Bo_Ry,

    You gave me an exellent solution, I would like to adjust a bit the formula to allow a differents time range gap for each column and adapt it next to my query table shape but for this I need to understand this quite complexe formula, can you describe how the formula works ?

    I would say i have a certain experience of excel but face this formula i feel i am still a novice.

  11. #11
    Registered User
    Join Date
    05-27-2020
    Location
    Guangzhou-China
    MS-Off Ver
    Office 2016
    Posts
    28

    Re: Split one colunm in severals columns in accordance with criterias

    Hello,

    Thanks Bo_Ry for your help i finally used your formula that I customized a bit to be able to split the time on severals collumn in accordance with each department work time. Now I am able to fully automatized the employee worktime.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to automatize severals actions
    By OoPan in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-15-2016, 03:14 AM
  2. [SOLVED] Plot Graph in accordance to data
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2016, 08:45 AM
  3. Set Decimal Places in accordance to different set of value
    By b311 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2012, 07:16 AM
  4. Sum in accordance to another cell
    By gilbert in forum Excel General
    Replies: 6
    Last Post: 07-06-2010, 05:48 AM
  5. Sumif formula needs to split 2 criterias of addition
    By 3smees23 in forum Excel General
    Replies: 4
    Last Post: 08-07-2009, 03:45 AM
  6. Sum 90% of values in a colunm
    By wlln001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2009, 12:25 PM
  7. Possible sum in colunm of numbers
    By Pedroluna in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2009, 02:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1