Need consolidated Total duration of the training hours spent by each employee

**vijavad** · 10-30-2023, 09:06 AM

Hi Team,

I have the below details of employees attendance from MS TEAMS downloaded file .

What i need

1 -I need NO OF DAYS excluding SAT & SUN based on FIRST join column data
2- I need the total duration from the column In-Meeting Duration in other column with format HH:MM:SS
3- If an employee joined only for few minutes it should only show in minutes with format MM:SS ( see example KIRAN from the below data last 2 rows)

EMP ID Name First Join Last Leave In-Meeting Duration
230456 Vijay 10/09/23, 2:03:31 PM 10/09/23, 6:03:11 PM 3h 59m 39s
230456 Vijay 10/09/23, 2:03:31 PM 10/09/23, 6:03:11 PM 3h 59m 39s
230456 Vijay 10/10/23, 2:09:53 PM 10/10/23, 6:00:55 PM 3h 30m 40s
230456 Vijay 10/10/23, 2:09:53 PM 10/10/23, 4:30:07 PM 2h 20m 14s
230456 Vijay 10/10/23, 4:35:17 PM 10/10/23, 4:55:53 PM 20m 35s
230456 Vijay 10/10/23, 5:11:04 PM 10/10/23, 6:00:55 PM 49m 51s
230456 Vijay 10/11/23, 2:01:22 PM 10/11/23, 6:00:06 PM 2h 9m 35s
230456 Vijay 10/11/23, 2:01:22 PM 10/11/23, 2:24:30 PM 23m 7s
230456 Vijay 10/11/23, 3:42:20 PM 10/11/23, 4:22:36 PM 40m 15s
230456 Vijay 10/11/23, 4:53:52 PM 10/11/23, 6:00:06 PM 1h 6m 13s
230456 Vijay 10/12/23, 2:00:43 PM 10/12/23, 6:05:04 PM 4h 4m 20s
230456 Vijay 10/12/23, 2:00:43 PM 10/12/23, 6:05:04 PM 4h 4m 20s
230456 Vijay 10/13/23, 2:15:37 PM 10/13/23, 6:00:44 PM 3h 41m 54s
230456 Vijay 10/13/23, 2:15:37 PM 10/13/23, 2:37:00 PM 21m 23s
230456 Vijay 10/13/23, 2:40:12 PM 10/13/23, 6:00:44 PM 3h 20m 31s
230456 Vijay 10/16/23, 1:58:08 PM 10/16/23, 6:20:02 PM 1h 23m 30s
230456 Vijay 10/16/23, 1:58:08 PM 10/16/23, 2:36:15 PM 38m 6s
230456 Vijay 10/16/23, 5:21:24 PM 10/16/23, 5:24:11 PM 2m 47s
230456 Vijay 10/16/23, 5:37:24 PM 10/16/23, 6:20:02 PM 42m 37s
230456 Vijay 10/17/23, 2:12:34 PM 10/17/23, 5:57:31 PM 3h 40m 19s
230456 Vijay 10/17/23, 2:12:34 PM 10/17/23, 3:21:44 PM 1h 9m 9s
230456 Vijay 10/17/23, 3:26:21 PM 10/17/23, 5:57:31 PM 2h 31m 10s
230456 Vijay 10/18/23, 2:01:05 PM 10/18/23, 5:58:00 PM 3h 56m 54s
230456 Vijay 10/18/23, 2:01:05 PM 10/18/23, 5:58:00 PM 3h 56m 54s
231786 Kiran 10/16/23, 1:58:08 PM 10/16/23, 2:36:15 PM 38m 6s
231786 Kiran 10/16/23, 5:21:24 PM 10/16/23, 5:24:11 PM 2m 47s

**etaf** · 10-30-2023, 09:29 AM

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

**vijavad** · 10-30-2023, 09:58 AM

Hi Team

our office laptops not support uploading files hence i have pasted the table in the body.

I am uploading the sheet for your reference of MS Office 2019 but i need formula for the same for Office365 excel version.

1 -I need NO OF DAYS excluding SAT & SUN based on FIRST JOIN column data
2- I need the total duration from the column In-Meeting Duration in other column with format HH:MM:SS
3- If an employee joined only for few minutes it should only show in minutes with format MM:SS ( see example KIRAN from the below data last 2 rows)

**etaf** · 10-30-2023, 10:38 AM

not great example - they are all 1 day and no expected results

everything is text so needs to be converted to real date and time using text functions - mid/right/left etc

If an employee joined only for few minutes

whats a few minutes as excel we need a criteria , 5mins, 10mins , 3mins - that will need an IF

see the time as it shows as o hh - otherwise may need VBA to reformat the cell

i have used
=NETWORKDAYS(DATE(20&MID(C2,7,2),LEFT(C2,2),MID(C2,4,2)),DATE(20&MID(D2,7,2),LEFT(D2,2),MID(D2,4,2)))

which gives 1 in all cases in your example , do you want something by employee number - just 1 entry

row 2 and 3 are the same ????????

for timevalue - i have used
=IFERROR(TIMEVALUE((0&":"&SUBSTITUTE(SUBSTITUTE(E2,"m ",":"),"s",""))),TIMEVALUE((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"h ",":"),"m ",":"),"s",""))))

maybe a better way

**vijavad** · 10-30-2023, 11:12 AM

Hi Etaf

Thanks for looking into the problem and for the solution.

In simple terms see what i am expecting.

1. The excel sheet which i shared with you refer to column " C " Vijay employee has 24 entries in column " C " if you see 10/09/2023 2 times, 10/10/2023 4 times . So like that i need overall no of days Vijay has attended the training . In this case he attended 8 days . Considering 10/09/2023 till 10/18/2023 excluding SAT(10/14/2023) & SUN (10/15/2023)

2. regarding the total duration for Vijay employee i need all the sum of hours from column " E " from E2 to E25 in the format HH:MM:SS.

I need formula in my excel Office 365 . Hope i have clarified your doubt now

**etaf** · 10-30-2023, 11:46 AM

will this sheet have loads of different training for the same employee

so for vija , could you have days not attending
are they all consecutive days
Considering 10/09/2023 till 10/18/2023
but what if you had on the sheet
Considering 10/09/2023 till 10/18/2023
and also
Considering 10/23/2023 till 10/30/2023

then that will count 9th to 30th excluding weekends
But that would be wrong

not sure if that is the case - if consecutive then Min(date), max(date) with networkdays will do that

**vijavad** · 10-30-2023, 12:10 PM

Hi Etaf

At least can you give a formula for all the below durations into one consolidated duration in HH:MM:SS.

In-Meeting Duration
3h 59m 39s
3h 59m 39s
3h 30m 40s
2h 20m 14s
20m 35s
49m 51s
2h 9m 35s
23m 7s
40m 15s
1h 6m 13s
4h 4m 20s
4h 4m 20s
3h 41m 54s
21m 23s
3h 20m 31s
1h 23m 30s
38m 6s
2m 47s
42m 37s
3h 40m 19s
1h 9m 9s
2h 31m 10s
3h 56m 54s
3h 56m 54s

**etaf** · 10-30-2023, 12:47 PM

same sort of question really , are all those durations for each employee to be summed regardless of the number of entries ????
same would apply to time, so if you do that , max/min will do again with helper column

i have a created a helper columns for converting the time to real time and then used that with a SUMIF()
=SUMIF(Data!$A$2:$A$100,'Need formula'!$A2,Data!$F$2:$F$100)

I also pull the emp id using unique and filter
so 365 version or i think 2021 versions only

in the data sheet column F has
=IFERROR(TIMEVALUE((0&":"&SUBSTITUTE(SUBSTITUTE(E2,"m ",":"),"s",""))),TIMEVALUE((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"h ",":"),"m ",":"),"s",""))))
to convert the time to real time

=UNIQUE(FILTER(Data!A2:A100,Data!A2:A100<>""))
pulls across all the emp id

then the SUM is using
=SUMIF(Data!$A$2:$A$100,'Need formula'!$A2,Data!$F$2:$F$100)

and the column needs to be formatted
[H]:mm:ss

to show hours when over 24 - as that will not show correctly

in column G and H - i have converted the dates to real dates - again as helpers
and then used

=NETWORKDAYS(MINIFS(Data!$H$2:$H$100,Data!$A$2:$A$100,'Need formula'!A2),MAXIFS(Data!$H$2:$H$100,Data!$A$2:$A$100,'Need formula'!A2))

to find the working days for emp id

**vijavad** · 12-06-2023, 11:57 AM

Hi Etaf

I hope you are doing well . i have attached excel sheet and looking for the below. please help.

I am using Office365 excel in my office laptop. Due to restrictions i have updated the information in excel 2016 from my personal laptop here in this thread.

If any one can provided formula it should work both in Office365 & 2016 versions that will be helpful.

I need formula in " Outcome sample sheet" tab in the attached excel sheet in column C & D. What i am looking is

In Outcome sample sheet - Need total number of days from Main Data sheet column " C ". Example For Emp id 289765 he attended from 21 Nov till 29 Nov excluding SAT & SUN the total no of days are 7 hence in Outcome sample sheet cell C4 should be 7 days and D2 cell should be total duration of all 7 days he attended that is sum of Main Data tab Cells E7 to E15 with format HH MM SS.

**AliGW** · 12-06-2023, 12:01 PM

Change your forum profile to the OLDEST version of Excel that solutions need to work for, NOIT the newest. Thanks.

Your duplicate thread has been closwd - please read the rules and continue here.

**vijavad** · 12-06-2023, 12:03 PM

Hi Ali

Could you please help me what mistake i did here and let me know how to Change the forum profile to the OLDEST version of Excel

**AliGW** · 12-06-2023, 12:06 PM

You said in the other thread that it needed to work in Excel 2016 - so change Office 365 in your profile to Excel 2016, please.

I am moving this out of the Office 365 section.

**vijavad** · 12-06-2023, 12:11 PM

Hi Ali Thanks a lot . I changed it please check and can i have some help on my thread " Need consolidated Total duration of the training hours spent by each employee"

**AliGW** · 12-06-2023, 12:22 PM

Thank you. You will get help when someone is able to offer it. Please be patient.

**vijavad** · 12-06-2023, 12:24 PM

Sure Ali i will wait. Thank you so much and have a great evening

**JeteMc** · 12-07-2023, 11:19 PM

I feel that the main issue is that the values in columns C and E are text which can be determined by widening the column and seeing that the values stay shifted to the left.
To convert the dates in column C use the following in column F: =DATEVALUE(LEFT(C2,SEARCH(",",C2)-1))
To convert the times in column E use the following in column H: =TIMEVALUE((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(SEARCH("h",E2)),"00h" & IF(ISERROR(SEARCH("m",E2)),"00m" & E2,E2),E2),"h",":"),"m",":"),"s","")))
To count unique days use the following in column G: =1/COUNTIFS(F$2:F$15,F2,A$2:A$15,A2)
The output can now be produced using a pivot table as modeled in the attached file.
Let us know if you have any questions.

**vijavad** · 12-09-2023, 01:34 PM

Hi JeteMc,

Thank you for your wonderful help and made my task easier in every day at my work. Once again i want to thank you for your valuable time and for your efforts.

**JeteMc** · 12-09-2023, 02:09 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**Czeslaw** · 12-09-2023, 02:45 PM

Power Query

Please Login or Register  to view this content.

Need consolidated Total duration of the training hours spent by each employee

LinkBack

Thread Tools

Rate This Thread

Display

Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Re: Need consolidated Total duration of the training hours spent by each employee

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Training Roster Tracking involving Employee Training and Groups

How to calculate 'per person/role total training hours'

[SOLVED] Sumif to determine total hours spent on a project

Calculating Total Number of Training Hours

Employee hours - total for each employee

Counting total hours for breaks / training at half hour intervals

total time spent for a specific employee

Bookmarks

Bookmarks

Posting Permissions