+ Reply to Thread
Results 1 to 5 of 5

I need help to generate attendance sheet by using excel 19

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    India
    MS-Off Ver
    Office 19
    Posts
    4

    I need help to generate attendance sheet by using excel 19

    I am going to prepare monthly attendance sheet based on the data that collected by google forms. My raw data's header is :

    Employee Name & ID: Designation: Working Address: Attendance Type: Date Time
    AAA (001) Support Staff Kolkata, India Time In 15/04/2021 08:21:16
    BBB (002) Support Staff Kolkata, India Time In 15/04/2021 08:21:43
    CCC (003) Support Staff Kolkata, India Time In 15/04/2021 08:37:35
    DDD (004) Support Staff Kolkata, India Time In 15/04/2021 08:41:14
    EEE (005) Support Staff Kolkata, India Time In 15/04/2021 08:43:16
    AAA (001) Support Staff Kolkata, India Time Out 15/04/2021 17:30:20
    BBB (002) Support Staff Kolkata, India Time Out 15/04/2021 17:15:10
    CCC (003) Support Staff Kolkata, India Time Out 15/04/2021 17:00:50
    DDD (004) Support Staff Kolkata, India Time Out 15/04/2021 17:01:25
    EEE (005) Support Staff Kolkata, India Time Out 15/04/2021 17:10:20
    FFF (006) Support Staff Kolkata, India Time In 16/04/2021 08:41:14
    FFF (006) Support Staff Kolkata, India Time Out 16/04/2021 18:10:01


    I wan to generate the monthly attendance sheet like below:

    Employee Name & ID: Designation: Working Address: 15/04/2021 16/04/2021
    Time In Time Out Total Hour Time In Time Out Total Hour
    AAA (001) Support Staff Kolkata, India 08:21:16 17:30:20 09:09 N N N
    BBB (002) Support Staff Kolkata, India 08:21:43 17:15:10 08:53 N N N
    CCC (003) Support Staff Kolkata, India 08:37:35 17:00:50 08:23 N N N
    DDD (004) Support Staff Kolkata, India 08:41:14 17:01:25 08:20 N N N
    EEE (005) Support Staff Kolkata, India 08:43:16 17:10:20 08:27 N N N
    FFF (006) Support Staff Kolkata, India N N N 08:41:14 18:10:01 09:28

    For more information, please see the attachment.
    Attached Files Attached Files

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

    Re: I need help to generate attendance sheet by using excel 19

    In real life, I have seen a specific employee have multifly IN/OUT for a same date.
    Below solution return the lastest IN/OUT time for an employee of single date.

    D21:
    Please Login or Register  to view this content.
    F21:
    Please Login or Register  to view this content.
    Copy/paste to other involving columns
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    04-16-2021
    Location
    India
    MS-Off Ver
    Office 19
    Posts
    4

    Re: I need help to generate attendance sheet by using excel 19

    Quote Originally Posted by bebo021999 View Post
    In real life, I have seen a specific employee have multifly IN/OUT for a same date.
    Below solution return the lastest IN/OUT time for an employee of single date.

    D21:
    Please Login or Register  to view this content.
    F21:
    Please Login or Register  to view this content.
    Copy/paste to other involving columns

    Solution Accepted. Thank you Sir.

  4. #4
    Registered User
    Join Date
    04-16-2021
    Location
    India
    MS-Off Ver
    Office 19
    Posts
    4

    Re: I need help to generate attendance sheet by using excel 19

    Dear Sir,
    Your solution is really amazing. I use this formula and prepared the sheet. Now I am facing another problem that is when I put this formula in Google Sheets then it is not work for me. Can you please help me again?

    For more details, please see the attachment.

    Thanks in advanced.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: I need help to generate attendance sheet by using excel 19

    You need to ask a moderator to move this thread to "Other Platforms" forum.

+ 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 macro to generate statistics on employee biometric monthly attendance
    By malathi1972 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-18-2018, 11:31 AM
  2. Excel Attendance Sheet issues and solutions
    By Tauheed Ahmed in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2016, 04:58 AM
  3. Replies: 9
    Last Post: 10-11-2014, 12:29 PM
  4. Attendance sheet using excel
    By antonio-helder2002 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-04-2014, 05:00 AM
  5. How to Analyze Attendance Sheet in Excel 2010
    By 9353PD in forum Excel General
    Replies: 0
    Last Post: 04-22-2013, 10:35 PM
  6. Macro? To help generate a student attendance list......
    By LMM21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2012, 05:06 AM
  7. Excel fuction - help required in Attendance Sheet?
    By NonStopLeo in forum Excel General
    Replies: 7
    Last Post: 03-12-2010, 06:42 AM

Tags for this Thread

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