Cumulative time calculation considering overlaps and gaps in excel database

[martin_b4]

Dear members, sorry for long explanation. Already too much time spent on this and it is already really urgent..

Task is: calculate netto employment (in years, months and days) of thousands employees. Gaps in employment must be considered and not included into employment time, overlapping employments may not be double counted. Goal is to calculate total netto time of employment (years, months, days) and day of „anniversary“ i.e. when employee reaches another full year of employment. Thanks a lot.

 

[bebo021999]

I used to consider using formulas (COUNTIF & SUMPRODUCT) to solve this requirement until I realized that the actual data involves thousands of employees, and using formulas could be time-consuming to process.

Therefore, I believe that the best solution is to use VBA. Before getting started, please attach a hypothetical file with shortened but representative data for us.
(use minisheet with XL2BB or attach file via gg drive, dropbox...)

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[martin_b4]

I used to consider using formulas (COUNTIF & SUMPRODUCT) to solve this requirement until I realized that the actual data involves thousands of employees, and using formulas could be time-consuming to process.

Therefore, I believe that the best solution is to use VBA. Before getting started, please attach a hypothetical file with shortened but representative data for us.
(use minisheet with XL2BB or attach file via gg drive, dropbox...)

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Thanks a lot. How is possible to attach file via gg drive or dropbox? I see pnly upload image option.. Thank you

 

[martin_b4]

I used to consider using formulas (COUNTIF & SUMPRODUCT) to solve this requirement until I realized that the actual data involves thousands of employees, and using formulas could be time-consuming to process.

Therefore, I believe that the best solution is to use VBA. Before getting started, please attach a hypothetical file with shortened but representative data for us.
(use minisheet with XL2BB or attach file via gg drive, dropbox...)

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

I would love to see SUMIF and Sumproduct solution/formula as well. Is it not a problem, that employees have different number of forme employers?

 

[bebo021999]

Thanks a lot. How is possible to attach file via gg drive or dropbox? I see pnly upload image option.. Thank you

Attach you file via gg drive or drop box. Then copy the link.
Just post the link here. Do not forget to share full.

 

[bebo021999]

I would love to see SUMIF and Sumproduct solution/formula as well. Is it not a problem, that employees have different number of forme employers?

Will try, once having the sample file.

 

[martin_b4]

Attach you file via gg drive or drop box. Then copy the link.
Just post the link here. Do not forget to share full.

excel

Shared with Dropbox

www.dropbox.com

Hope it works

 

[bebo021999]

Where is your desired output? Please input it manually and we will help with formula/code script.

 

[martin_b4]

Where is your desired output? Please input it manually and we will help with formula/code script.

I am terrible sorry, I can not Edit the file right now. Will be ale to manage in several hours.. Wished result:In an separate column should be Total employment until the end of current month. Or there might be 3 columns: years, months and days of total employment until the end of current month. For Adam its 34 years, 6 months and 30 days if I am calculating right.

And final column is "fututre anniversary" when employee reaches another full year of employment. In case of Adam its april 2024.

 

[martin_b4]

Where is your desired output? Please input it manually and we will help with formula/code script.

filled_example.xlsx

Shared with Dropbox

www.dropbox.com