Excel list to show changes in working schedule

[kaz123]

Hello,

I need some help with a query. I have a big employee list (from HR system) showing all the different types changes in the record including changes in weekly working hours, this means some employee could be on the list multiple times (with the same unique employee ID) if they have more than one change. The list has a number of columns including Employee ID, Change effective Start, Change effective End, Hours. - what I am trying to find out is all the employees who changed their hours between the financial year (e.g. 01 April 2019 to 31 March 2020) and to show - for example, if the employee has 10 entries in the list (for all sorts of changes) and one entry for showing changes in their hours on 05/08/2019, then it should show that line and also the line with the previous value.

Hope this makes sense, the list returns over 10k rows and essentially what I am trying to do is return any changes in hours between the date range, and if they haven't changed their hours then return the latest row which will show the current hours.

 

[Rijnsent]

Hi Kaz,
to find out the number of records per employee, a Pivot Table could be helpful. That would also allow you to filter for all records within your financial year. An alternative is to add 1 or more columns to your data for filtering. E.g. you could make an IF formula for just the date of the record (TRUE if it's in your financial year and FALSE if it's not).
If you have more in-detail questions: please do post a chunk of your (anonimized) data and desired outcome here, preferably using XL2BB (or alternatively a dropbox/googledrive link).
Cheers,
Koen

 

[kaz123]

Thanks a lot for your advice Koen, will try as you suggested.