# Formulas Needed for Report Comparison



## Hscott1982 (Dec 29, 2022)

Hello lovely Excel friends...I need help. Getting back into my daily excel use and trying to dust off my fomulas knowledge.

I have 2 reports in Excel that I need to cross compare. Both reports have Employee Names and ID numbers, both have varying dates of attendance incidents. I need to compare to see the differences of the dates. 

Report 1 is the Master Report, Report 2 is for Verification. 

Thoughts: How can I compare the dates across reports for each Employee and point out the differences? 

Thank you in advance for your amazing help!


----------



## Sufiyan97 (Dec 29, 2022)

Hi & Welcome to MrExcel

Please post your sample data using below link


----------



## Hscott1982 (Dec 30, 2022)

Testing Attendance Formulas.xlsxK13Warren


----------



## Sufiyan97 (Dec 30, 2022)

The sheet you posted is black, please upload again.


----------



## Hscott1982 (Jan 3, 2023)

Sufiyan97 said:


> The sheet you posted is black, please upload again.


Hello, 
I tried the link you provided, what I responded above is what it gave me. This is a work computer, so somethings are locked down.


----------



## Fluff (Jan 3, 2023)

You need to select a range of cells before clicking "mini sheet", not just a single cell.


----------



## Hscott1982 (Jan 3, 2023)

Tab 1 "Warren"
Testing Attendance Formulas.xlsxABCDEFG1Employee NumberIncident DATECREWDEPTCLOCK#TeammateFULL or HALF21010143511/4/2019D1311206Fulton, AaronUnexcused - Half Incident3101014358/10/2020D1311206Fulton, AaronUnexcused - Full Incident4101014358/28/2020D1311206Fulton, AaronUnexcused - Full Incident51010143510/11/2020D1311206Fulton, AaronUnexcused - Full Incident6101014359/10/2021D1311206Fulton, AaronUnexcused - Half Incident7101014353/28/2021D1311206Fulton, AaronUnexcused - Full Incident8101014354/14/2021D1311206Fulton, AaronUnexcused - Full Incident9101014355/17/2021D1311206Fulton, AaronUnexcused - Full Incident101010143510/19/2021D1311206Fulton, AaronUnexcused - Full Incident111010143511/15/2021D1311206Fulton, AaronUnexcused - Full Incident12101014352/7/2022D1311206Fulton, AaronUnexcused - Full Incident13101014354/28/2022D1311206Fulton, AaronUnexcused - Full Incident14101014358/4/2022D1311206Fulton, AaronUnexcused - Full Incident15101014358/31/2022D1311206Fulton, AaronUnexcused - Full Incident16100991741/20/2020A1311182Hawkins, AaronUnexcused - Full Incident17100991747/12/2020A1311182Hawkins, AaronUnexcused - Full Incident181009917412/28/2020B1311182Hawkins, AaronUnexcused - Full Incident19100991749/4/2021A1311182Hawkins, AaronUnexcused - Full Incident20100991746/16/2022A1311182Hawkins, AaronUnexcused - Full Incident211015379711/4/2019D1311423Lowe, AaronUnexcused - Half Incident221015379712/28/2019C1311423Lowe, AaronUnexcused - Full Incident23101537973/3/2020C1311423Lowe, AaronUnexcused - Full Incident24101537976/13/2020C1311423Lowe, AaronUnexcused - Full Incident25101537979/19/2020C1311423Lowe, AaronUnexcused - Full Incident261015379710/23/2020D1311423Lowe, AaronUnexcused - Full Incident27101537979/25/2021D1231423Lowe, AaronUnexcused - Half Incident281015379711/25/2021D1231423Lowe, AaronUnexcused - Half Incident29101537972/14/2021D1231423Lowe, AaronUnexcused - Full Incident30101537973/27/2021D1231423Lowe, AaronUnexcused - Full Incident31101537975/8/2021D1231423Lowe, AaronUnexcused - Full Incident32101537971/21/2020C1311423Lowe, AaronUnexcused - Half Incident33101537977/11/2020C1311423Lowe, AaronUnexcused - Half Incident341015379710/30/2021C1231423Lowe, AaronUnexcused - Full Incident35101537972/24/2022A1231423Lowe, AaronUnexcused - Full Incident36101860407/11/2020C1691643Moore, AaronUnexcused - Full Incident37101860403/6/2021C1691643Moore, AaronUnexcused - Half Incident38101860409/26/2020B1691643Moore, AaronUnexcused - Full Incident391018604010/2/2021C1691643Moore, AaronUnexcused - Half Incident40101860401/13/2021C1691643Moore, AaronUnexcused - Full Incident41101860409/25/2021D1691643Moore, AaronUnexcused - Full Incident42101860407/14/2021C1691643Moore, AaronUnexcused - Half Incident43101860402/10/2022C1691643Moore, AaronUnexcused - Full Incident44101860403/17/2022D1691643Moore, AaronUnexcused - Full Incident45101860406/6/2022C1311643Moore, AaronUnexcused - Full Incident46101860408/11/2022C1311643Moore, AaronUnexcused - Full IncidentWarren

Tab 2 "Dayforce"
Testing Attendance Formulas.xlsxABCDEFG1Employee NumberTeammateEmployment StatusPay TypeIncident DateIncident NameAttendance Points210101435Fulton, AaronActiveHourly Non-Exempt8/10/2020Attendance History 1pt1.00000310101435Fulton, AaronActiveHourly Non-Exempt8/28/2020Attendance History 1pt1.00000410101435Fulton, AaronActiveHourly Non-Exempt10/11/2020Attendance History 1pt1.00000510101435Fulton, AaronActiveHourly Non-Exempt3/28/2021Attendance History 1pt1.00000610101435Fulton, AaronActiveHourly Non-Exempt4/14/2021Attendance History 1pt1.00000710101435Fulton, AaronActiveHourly Non-Exempt5/17/2021Attendance History 1pt1.00000810101435Fulton, AaronActiveHourly Non-Exempt9/10/2021Attendance History .5pt0.50000910101435Fulton, AaronActiveHourly Non-Exempt10/19/2021Attendance History 1pt1.000001010101435Fulton, AaronActiveHourly Non-Exempt11/15/2021Attendance History 1pt1.000001110101435Fulton, AaronActiveHourly Non-Exempt2/7/2022Attendance History 1pt1.000001210101435Fulton, AaronActiveHourly Non-Exempt4/28/2022Attendance History 1pt1.000001310101435Fulton, AaronActiveHourly Non-Exempt8/4/2022Attendance History 1pt1.000001410101435Fulton, AaronActiveHourly Non-Exempt8/31/2022Attendance History 1pt1.000001510101435Fulton, AaronActiveHourly Non-Exempt11/10/2022Hours Reduction (0P)0.000001610101435Fulton, AaronActiveHourly Non-Exempt11/14/2022Hours Reduction (0P)0.000001710101435Fulton, AaronActiveHourly Non-Exempt11/19/2022Hours Reduction (0P)0.000001810101435Fulton, AaronActiveHourly Non-Exempt11/20/2022Hours Reduction (0P)0.000001910101435Fulton, AaronActiveHourly Non-Exempt11/23/2022Hours Reduction (0P)0.000002010101435Fulton, AaronActiveHourly Non-Exempt11/28/2022Hours Reduction (0P)0.000002110101435Fulton, AaronActiveHourly Non-Exempt11/29/2022Absence1.000002210101435Fulton, AaronActiveHourly Non-Exempt12/4/2022Hours Reduction (0P)0.000002310101435Fulton, AaronActiveHourly Non-Exempt12/16/2022Hours Reduction (0P)0.000002410101435Fulton, AaronActiveHourly Non-Exempt12/22/2022Hours Reduction (0P)0.000002510099174Hawkins, AaronActiveHourly Non-Exempt1/20/2020Attendance History 1pt1.000002610099174Hawkins, AaronActiveHourly Non-Exempt7/12/2020Attendance History 1pt1.000002710099174Hawkins, AaronActiveHourly Non-Exempt12/28/2020Attendance History 1pt1.000002810099174Hawkins, AaronActiveHourly Non-Exempt9/4/2021Attendance History 1pt1.000002910099174Hawkins, AaronActiveHourly Non-Exempt6/16/2022Attendance History 1pt1.000003010099174Hawkins, AaronActiveHourly Non-Exempt11/7/2022Hours Reduction (0P)0.000003110099174Hawkins, AaronActiveHourly Non-Exempt11/13/2022Hours Reduction (0P)0.000003210099174Hawkins, AaronActiveHourly Non-Exempt11/16/2022Hours Reduction (0P)0.000003310099174Hawkins, AaronActiveHourly Non-Exempt12/19/2022Hours Reduction (0P)0.000003410153797Lowe, AaronActiveHourly Non-Exempt1/21/2020Attendance History .5pt0.500003510153797Lowe, AaronActiveHourly Non-Exempt3/3/2020Attendance History 1pt1.000003610153797Lowe, AaronActiveHourly Non-Exempt6/13/2020Attendance History 1pt1.000003710153797Lowe, AaronActiveHourly Non-Exempt7/11/2020Attendance History .5pt0.500003810153797Lowe, AaronActiveHourly Non-Exempt9/19/2020Attendance History 1pt1.000003910153797Lowe, AaronActiveHourly Non-Exempt10/23/2020Attendance History 1pt1.000004010153797Lowe, AaronActiveHourly Non-Exempt2/14/2021Attendance History 1pt1.000004110153797Lowe, AaronActiveHourly Non-Exempt3/27/2021Attendance History 1pt1.000004210153797Lowe, AaronActiveHourly Non-Exempt5/8/2021Attendance History 1pt1.000004310153797Lowe, AaronActiveHourly Non-Exempt9/25/2021Attendance History .5pt0.500004410153797Lowe, AaronActiveHourly Non-Exempt10/30/2021Attendance History 1pt1.000004510153797Lowe, AaronActiveHourly Non-Exempt11/25/2021Attendance History .5pt0.500004610153797Lowe, AaronActiveHourly Non-Exempt2/24/2022Attendance History 1pt1.000004710153797Lowe, AaronActiveHourly Non-Exempt10/29/2022Attendance History 1pt1.000004810153797Lowe, AaronActiveHourly Non-Exempt11/23/2022Hours Reduction (0P)0.000004910153797Lowe, AaronActiveHourly Non-Exempt11/25/2022Hours Reduction (0P)0.000005010153797Lowe, AaronActiveHourly Non-Exempt11/29/2022Hours Reduction (0P)0.000005110153797Lowe, AaronActiveHourly Non-Exempt12/9/2022Hours Reduction (0P)0.000005210153797Lowe, AaronActiveHourly Non-Exempt12/10/2022Hours Reduction (0P)0.000005310153797Lowe, AaronActiveHourly Non-Exempt12/11/2022Hours Reduction (0P)0.000005410153797Lowe, AaronActiveHourly Non-Exempt12/13/2022Hours Reduction (0P)0.000005510153797Lowe, AaronActiveHourly Non-Exempt12/20/2022Hours Reduction (0P)0.000005610186040Moore, AaronActiveHourly Non-Exempt7/11/2020Attendance History 1pt1.000005710186040Moore, AaronActiveHourly Non-Exempt9/26/2020Attendance History 1pt1.000005810186040Moore, AaronActiveHourly Non-Exempt1/13/2021Attendance History 1pt1.000005910186040Moore, AaronActiveHourly Non-Exempt3/6/2021Attendance History .5pt0.500006010186040Moore, AaronActiveHourly Non-Exempt7/14/2021Attendance History .5pt0.500006110186040Moore, AaronActiveHourly Non-Exempt9/25/2021Attendance History 1pt1.000006210186040Moore, AaronActiveHourly Non-Exempt10/2/2021Attendance History .5pt0.500006310186040Moore, AaronActiveHourly Non-Exempt2/10/2022Attendance History 1pt1.000006410186040Moore, AaronActiveHourly Non-Exempt3/17/2022Attendance History 1pt1.000006510186040Moore, AaronActiveHourly Non-Exempt6/6/2022Attendance History 1pt1.000006610186040Moore, AaronActiveHourly Non-Exempt8/11/2022Attendance History 1pt1.000006710186040Moore, AaronActiveHourly Non-Exempt11/30/2022Hours Reduction (0P)0.000006810186040Moore, AaronActiveHourly Non-Exempt12/11/2022Absence1.000006910186040Moore, AaronActiveHourly Non-Exempt12/19/2022Hours Reduction (0P)0.00000DayForce


----------



## Alex Blakenburg (Jan 3, 2023)

It depends a little on how you want the results to look, which will probably depend on what your next steps are after identifying the differences.
Power Query will be my choice for giving you a list of items in Warren and not in Dayforce and a list for or items in Daysforce and not in Warren.
It can also give you a full list showing empty data where the data doesn't exist in either list.

You could also do an formula on Warren looking up DayForce identifying missing and one of Dayforce looking up Warrent to identify missing.
(you would need to address the issue that your date in Warren is right aligned and recognised as a date and the date in DayForce is left aligned which indicates it may be stored as text)

VBA is also an option but you would need to be much more specific on what you want your output to look like.

Note: Difference based on the information provided simply means in one list and not in the other.


----------



## Hscott1982 (Jan 6, 2023)

I really would just like it to cross reference each employee and highlight any differences of the attendance dates on the Dayforce report.


----------

