sort out attendance logs of staff

sumsaam

sumsaam

Board Regular
Joined
Dec 31, 2012
Messages
82
Office Version
  1. 2010
Platform
  1. Windows
27-04-2023.xlsx
ABCDEFGHIJKLMNO
1Source
2Target
3Emp IDFirst NameDateTimeID1
41Sumsaam01-04-202311:00NameSumsaam
51Sumsaam01-04-202311:53Date1234
61Sumsaam01-04-202323:06First log before 8:00AM if any00:48
71Sumsaam02-04-202312:061s log after 8:00AM11:0012:0611:17
81Sumsaam02-04-202322:432nd log11:5322:43
91Sumsaam04-04-202300:483rd log23:06
101Sumsaam04-04-202311:174th log
113Mustafa01-04-202311:235th log
123Mustafa01-04-202323:066th log
133Mustafa02-04-202312:48ID3
143Mustafa02-04-202322:32NameMustafa
153Mustafa03-04-202300:59Date1234
163Mustafa03-04-202316:34First log before 8:00AM if any00:59
173Mustafa04-04-202312:081s log after 8:00AM11:2312:4816:3412:08
183Mustafa04-04-202322:362nd log23:0622:3222:36
193rd log
204th log
215th log
226th log
23
24
Sheet




Hi,
I have data in form of source table and i want to convert that data in target table through formula, Thank you
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi, try this:
I've added a helper column to store employee ID.

Mr excel questions 35.xlsm
ABCDEFGHIJKLMNOPQRST
1Source
2Target
3Emp IDFirst NameDateTime1
41Sumsaam2023-04-0111:00:00IDSumsaam
51Sumsaam2023-04-0111:53:00Date12341234
61Sumsaam2023-04-0123:06:00First log before 8:00AM if any1   00:48:0000:48
71Sumsaam2023-04-0212:06:001s log after 8:00AM111:00:0012:06:00 11:17:0011:00:0012:06:0011:17:00
81Sumsaam2023-04-0222:43:002nd log111:53:0022:43:00  11:53:0022:43:00
91Sumsaam2023-04-0400:48:003rd log123:06:00   23:06:00
101Sumsaam2023-04-0411:17:004th log1    
113Mustafa2023-04-0111:23:005th log1    
123Mustafa2023-04-0123:06:006th log1    
133Mustafa2023-04-0212:48:00ID3
143Mustafa2023-04-0222:32:00NameMustafa
153Mustafa2023-04-0300:59:00Date12341234
163Mustafa2023-04-0316:34:00First log before 8:00AM if any3  00:59:00 00:59
173Mustafa2023-04-0412:08:001s log after 8:00AM311:23:0012:48:0016:34:0012:08:0011:23:0012:48:0016:34:0012:08:00
183Mustafa2023-04-0422:36:002nd log323:06:0022:32:00 22:36:0023:06:0022:32:0022:36:00
193rd log3    
2014th log3    
215th log3    
226th log3    
23
sumsaam
Cell Formulas
RangeFormula
L5,P5,L15,P15L5=DATE(2023,4,1)
M5:O5,Q5:S5,M15:O15,Q15:S15M5=1+L5
L6:O6L6=IFERROR(AGGREGATE(15,6,1/(1/((--(L$5=$C$4:$C$18))*(--($K6=$A$4:$A$18))*(--($D$4:$D$18<TIME(8,0,0)))*($D$4:$D$18))),1),"")
L7:O12L7=IFERROR(AGGREGATE(15,6,1/(1/((--(L$5=$C$4:$C$18))*(--($K7=$A$4:$A$18))*(--($D$4:$D$18>=TIME(8,0,0)))*($D$4:$D$18))),ROWS($L$7:L7)),"")
L16:O16L16=IFERROR(AGGREGATE(15,6,1/(1/((--(L$15=$C$4:$C$18))*(--($K16=$A$4:$A$18))*(--($D$4:$D$18<TIME(8,0,0)))*($D$4:$D$18))),1),"")
L17:O22L17=IFERROR(AGGREGATE(15,6,1/(1/((--(L$15=$C$4:$C$18))*(--($K17=$A$4:$A$18))*(--($D$4:$D$18>=TIME(8,0,0)))*($D$4:$D$18))),ROWS($L$18:L18)),"")
 
Upvote 0
Solution
27-04-2023.xlsx
ABCDEFGHIJKLMNOP
1Source
2Target
3Emp IDFirst NameDateTimeID1
41Sumsaam01-04-202311:00NameSumsaam
51Sumsaam01-04-202311:53Date01-04-202302-04-202303-04-202304-04-2023
61Sumsaam01-04-202323:06First log before 8:00AM if any1 00:48
71Sumsaam02-04-202312:061s log after 8:00AM1  11:17
81Sumsaam02-04-202322:432nd log111:5322:43
91Sumsaam04-04-202300:483rd log123:06
101Sumsaam04-04-202311:174th log1
113Mustafa01-04-202311:235th log1
123Mustafa01-04-202323:066th log1
133Mustafa02-04-202312:48ID3
143Mustafa02-04-202322:32NameMustafa
153Mustafa03-04-202300:59Date01-04-202302-04-202303-04-202304-04-2023
163Mustafa03-04-202316:34First log before 8:00AM if any3    
173Mustafa04-04-202312:081s log after 8:00AM3    
183Mustafa04-04-202322:362nd log323:0622:3222:36
193rd log3
204th log3
215th log3
226th log3
23
Sheet1
Cell Formulas
RangeFormula
M15:O15,M5:O5M5=1+L5
L5,L15L5=DATE(2023,4,1)
L6L6=IFERROR(AGGREGATE(15,6,1/(1/((--(L$5=$C$4:$C$18))*(--($K6=$A$4:$A$18))*(--($D$4:$D$18<TIME(8,0,0)))*($D$4:$D$18))),1),"")
L7:M7L7=IFERROR(AGGREGATE(15,6,1/(1/((--(L$5=$C$4:$C$18))*(--($K7=$A$4:$A$18))*(--($D$4:$D$18>=TIME(8,0,0)))*($D$4:$D$18))),ROWS($L$7:L7)),"")
L16:O16L16=IFERROR(AGGREGATE(15,6,1/(1/((--(L$15=$C$4:$C$18))*(--($K16=$A$4:$A$18))*(--($D$4:$D$18<TIME(8,0,0)))*($D$4:$D$18))),1),"")
L17:O17L17=IFERROR(AGGREGATE(15,6,1/(1/((--(L$15=$C$4:$C$18))*(--($K17=$A$4:$A$18))*(--($D$4:$D$18>=TIME(8,0,0)))*($D$4:$D$18))),ROWS($L$18:L18)),"")




Entered Formula in L7. but showing blank Cell
 
Upvote 0
sumsaam said:
Entered Formula in L7. but showing blank Cell
Click to expand...

not sure why this would happen. As you can see it works in the xl2bb snippet. Have you entered all data and formulas in the exact same place as how I have done it in the recommended solution? Also, be sure the cell formats are appropriate.
 
Upvote 0
You may want to check your date formats to confirm that both sets (in C4 and down...and in L5 and across) are consistent. For example, is 01-04-2023 the 4th of January or the 1st of April? For a quick spot check, enter
Excel Formula: 
=C4=L5
in an empty cell to check whether the first apparent matching date yields a TRUE or FALSE.
 
Upvote 0
=l5=c4 shows false , what is means? i have set same format for both cells even it shows false
 
Upvote 0
sumsaam said:
=l5=c4 shows false , what is means? i have set same format for both cells even it shows fals
Click to expand...
hange the format in I5 and C4 to general and expand the decimal view to maximum digits. Visually look at the values. Also, do a subtractioin I5-C4 in a general formatted cell with maximum decimals.

You may have some time component in your dates.
 
Upvote 0
Both of those cells appear to have 01-04-2023 in them, so we would expect the result to be TRUE (I am assuming you agree with this?). The entry in cell L5 is formed with a formula:
Excel Formula: 
=DATE(2023,4,1)
which is unambiguous because it's syntax requires inputs of year, month, and day, in that order. So L5 represents the 1st day of April in 2023. I do not know what is intended by 01-04-2023 in cell C4, but I believe you want it to be the same date in this example (1st day of April in 2023). Your system is probably interpreting 01-04-2023 as the 4th day of January. To confirm this, edit C4 (temporarily) to read as 04-01-2023 to confirm whether the logic formula I mentioned returns a TRUE. If it does, then you will want to examine your date formats. This may be something caused by either your regional settings or local cell formatting.

Here is a slightly modified version suggested by @awoohaw that replaces the row counting in the AGGREGATE function with simple references to a column that you already have to track the log number of the entries for each day. This means that you can copy the 7 cells in K6:K12 and paste them in each summary block for each staff member and drag right across the date range without having to update the row counting component of the formula. But the source of your problem at the moment appears to be associated with the date formatting. In the example below, you will see that I changed some dates in column C to match with the dates in the summary tables...and the formulas copy the times into the appropriate locations. Alternatively, if you are comfortable with the date representation shown in column C, then you may want to change the formula in K5, K15, etc., to ensure that the correct date range of interest is shown in the summary tables.

MrExcel_20230523.xlsx
ABCDHIJKLMN
1Source
2Target
3Emp IDFirst NameDateTimeID1
41Sumsaam4/1/202311:00NameSumsaam
51Sumsaam4/1/202311:53First Log…Log EntryEmp ID \ Date4/1/20234/2/20234/3/20234/4/2023
61Sumsaam4/1/202323:06before 8:00 AM11   0:48
71Sumsaam2/4/202312:06after 8:00 AM1111:00  11:17
81Sumsaam2/4/202322:432111:53   
91Sumsaam4/4/20230:483123:06   
101Sumsaam4/4/202311:1741    
113Mustafa1/4/202311:2351    
123Mustafa4/1/202323:0661    
133Mustafa4/2/202312:483
143Mustafa2/4/202322:32Mustafa
153Mustafa3/4/20230:594/1/20234/2/20234/3/20234/4/2023
163Mustafa3/4/202316:34before 8:00 AM13    
173Mustafa4/4/202312:08after 8:00 AM1323:0612:48 12:08
183Mustafa4/4/202322:3623   22:36
1933    
2043    
2153    
2263    
Sheet4
Cell Formulas
RangeFormula
K5,K15K5=DATE(2023,4,1)
L5:N5,L15:N15L5=1+K5
K6:N6,K16:N16K6=IFERROR(AGGREGATE(15,6,1/(1/((--(K$5=$C$4:$C$18))*(--($J6=$A$4:$A$18))*(--($D$4:$D$18<TIME(8,0,0)))*($D$4:$D$18))),1),"")
K7:N12,K17:N22K7=IFERROR(AGGREGATE(15,6,1/(1/((--(K$5=$C$4:$C$18))*(--($J7=$A$4:$A$18))*(--($D$4:$D$18>=TIME(8,0,0)))*($D$4:$D$18))),$I7),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Calculating quarterly hours with rounding.
Replies
5
Views
1K
RonB1111
R
D
  • Question
Enable buttons based on logged in user.
2
Replies
11
Views
3K
dkotula
D

Forum statistics

Threads
1,216,105
Messages
6,128,860
Members
449,472
Latest member
ebc9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top