Forum Discussion
Solved
How to count periods of absence in excel
Hey, I am creating a spreadsheet to track holidays and absence. I need to figure out a way of counting periods of absence. For example, in the attachment I want TEST 1 to show as one period of absenc...
What do you see when you open the sample workbook attached below?
HansVogelaar Thank you, I'm one step closer now! That doesn't quite get what I'm after though, I need TEST 1 to show as one period and so on as over a weekend should still count as one period. It should only count as more if there is a working day between periods.
What do you see when you open the sample workbook attached below?
Hi Hans
Am picking up an old thread you have helped with.
I have used your period function for my calculation but as we are using several reasons for absenteism the outcome is current double counting some periods.
For example i could have an employee being sick for 5 days and this will be recorded as 3 days of sick holiday (SH) meaning that he will use his holiday allocation for 3 days and 2 days as sick (S) which would be unpaid.
So the formula below would need to be able to look at different reason code
=Periods(last_year_dates,OFFSET(last_year_dates,12,0),"S")
Do you have any suggestion on how i could achieve this?
Thankyou very much.
Do you mean that the function should count the 3 SH days and 2 S days as one period?
Yes this is what I mean because we record it in two different way but this is the same period.
Day 1 Day 2 Day 3 Day 4 Day 5 SH SH SH S S Again, this could also be over weekends
Hope it clarifies.
- Hey, that's what I am after thank you. I have copied that formula into my sheet and it works as desired now
