#solved Hello there. Could someone help me creating a formula or VBA to calculate absence periods.
#solved
Hello there. Could someone help me creating a formula or VBA to calculate absence periods. I have data in formats as shown on pictures. Thank you
Replies
maybe if you explain what you mean? how you need it to be calculated -- Criteria, and where you want the results, and what kind of results you want?????????????????????????? ---------- WHAT VERSION OF EXCEL ???? --------------
Dave McAllister I just need to calculate periods of absence per employee. It can be calculated at the top (row 1 on second picture). Each employee has their column.
Dave McAllister office 365
Dagmara Joanna the top data is all over the place - why cant it be entered in the bottom table, much better organization. The top one is just a random mess of dates, names, and few different kinds of data.
Dave McAllister whatever is better / easier. Layout of the file can be changed if needed.
Dagmara Joanna but it looks like you already have things set up to get the count of the days for other things, just add in the ABSENT days as A.
Dave McAllister the file only calculates number of days absent but no periods. I want to know how many times an employee was off sick. 1 period could be 2 weeks, they will come back to work for a day or so and will be absent again which would start their second period of absence.
Dagmara Joanna ok so if they are ABS 2 days, (Monday-Tuesday) work wednesday but ABS Thru-Fri -- you have one cell what do you want returned? and does it have to be for more than 1 day or if they do M-W-F ABS but work T-Thr so that is three periods??? again, what do you want returned?
Dave McAllister if they are absent consecutive days then it is just one period. So if they are ABS Mon and Tue, work Wed but ABS again on Thu and Fri the result should be 2. I would need a sum of periods
I am using Power Query to produce data from the first picture. I’m not sure if PQ can do what I want.
Dagmara Joanna please correct me if I understood this wrong so in example table employee2 was absent in week 8th and week 7th for 8 days . In the table employee2 was absent for more than 8 time but you need absent count happened in two weeks of period,,is that correct?
Vishal Jagtap no, employee 2 was absent for 8 days in those weeks but that would count as 1 period as they were consecutive days. I have data for the whole year and need the calculation dynamic.
Dagmara Joanna got it will try and let you know soon. It will be more helpful if you could attached sample file.
WORKING ON SECOND PIC ONLY ------ In Column D in the cell you want the result --- you only show to row 34, so expand the range to the end. Note each range is offset by one row =COUNTIFS(D10:D34,"A",D9:D33,"<>A") change D34 to the last day in your range, and the second must be the same size but up one row.
Dave McAllister thank you. I’ll test it in the morning.
Dagmara Joanna Just enter all you data on the second worksheet. its much simpler and easier when everything is in one place.. The first table might be cool for looks, but logistic its a bit more calculations.
Dave McAllister how can I make weekends to count towards the same period of absence. With the formula you have given me the issue I am having is that weekends are giving me additional period of absence as there is no data in the cell. I could add absence for the weekend but the total count of sick days will be incorrect.
Dagmara Joanna Change the formula to this::: =SUMPRODUCT((D10:D34="A")(D9:D33<>"A")(WEEKDAY($C9:$C33,2)<6))
Assuming Cell C9 is Blank !!!!!
