Hi,
I have two dates that are absolute, a start date, and an end date. In between these dates, there MIGHT be multiple other dates (up to 7 or so). Corresponding with each date is a sick leave %.
Then in between each date, there will be changes of sick leave status in the period. The workbook is a log status/Statistical program over pregnant women and the pregnancy.
Below is an example of dates.
01.01.2013, 0%
01.05.2013, 50%
30.09.2013, 50%
Or:
01.01.2013, 0%
01.03.2013, 40 %
01.06.2013, 60 %
30.09.2013, 60 %.
The pregnancy period can be divided in 38 weeks. Then I want to know that for the first 8 weeks, the woman had a sick leave of 0 %. For the Next 13 weeks, she had a sick leave of 60 %, and for the last 17 weeks she had a sick leave of 60 %.
And I want it to be divided in each week, so i.e. A1= week 1, 0%, A2=week 2, 0%...A14=week 14, 60 %.
If I wanted to follow only one woman, I am able to use a IF-function, but the problem arises as there will be multiple woman, With completely different dates. Each start date will however always be week 1, so that I can compare the sick leave % in the different weeks of the pregnancy.
Any good ideas on how to solve this? At first I thought about manually writing in the sick leave % in each week. But by instead using the dates the sick leave % actually changes, it will be much less demanding, if there is a formula/function that can do this. Hopefully this is understandable. I’ve attached an Excel-file, that hopefully shows what I’m trying to achieve.
Bookmarks