Forum Discussion
Compare diffrently structured time periods for errors
Not sure I understood correctly what is required. If the goal is to convert range as in File1 to one which is in File2
(from most left here to most right one), variant could be using formulas:
in F2
=IFERROR(INDEX(B:B, AGGREGATE(15,6,1/ ($B$2:INDEX($B:$B,COUNTA($B:$B))<>$C$1:INDEX($C:$C,COUNTA($B:$B)-1)) / ($B$2:INDEX($B:$B,COUNTA($B:$B)) <> 0) * ROW($B$2:INDEX($B:$B,COUNTA($B:$B))), ROW()-ROW($F$1) )), "")copy and paste this cell to E2.
In G2
=IFERROR(INDEX(C:C, AGGREGATE(15,6,1/ ($C$2:INDEX($C:$C,COUNTA($B:$B))<>$B$3:INDEX($B:$B,COUNTA($B:$B)+1)) / ($B$2:INDEX($B:$B,COUNTA($B:$B)) <> 0) * ROW($B$2:INDEX($B:$B,COUNTA($B:$B))), ROW()-ROW($F$1) )), "")Select E2:G2 and drag down till empty cells appear.
Please read this information. It is of great help to you and everyone who would like to help.
https://techcommunity.microsoft.com/t5/excel/welcome-to-your-excel-discussion-space/m-p/2204395
Then, if I may recommend you, add a file (without sensitive data) and explain exactly what your plan is.
Thank you for your understanding and patience 🙂
Nikolino
There are always many ways of doing this in Excel.
Here is a small example with pivot, where you can expand the table and continue to calculate all times.
The time has been converted to decimal calculation: (Endtime - Startime) * 24
You can change this as you wish, see the formula in the table.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- Thank you very much for your quick respond but I think there was a misunderstanding! As I understand it you took the data from file1 and calculated the sum of the time periods and then summarized your findings in the pivot table.
However I need to find the errors when comparing file1 and file2. I am looking for the following type of error: One file has a time period which is not listed in the other file. The sum of hours for one day could be the same even though the time periods are not the same. I am not proficient in Excel but I think that some kind of Macro which restructers file1 to look like file2 (excluding the 00:00-00:00 time periods) would be good.Have a look at this suggested solution in the inserted file ... although I don't think that's what you are looking for, my knowledge of Pivot ends here.
If maybe Mr. Baklan had time :))
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
