Spreadsheet to total weeks up to 52

[Mel]

I have a spreadsheet that looks at the last date an employee was given
an increment. They are due a new increment 52 weeks from that date,
first Monday following. Calculating this is easy. What I have to
now include is periods that do not count.

ie. Last increment date is April 2, 2001
1. They work from April 2, 2001 to Oct. 31, 2001. (on time)
2. the period from Nov. 1, 2001 to March 31, 2002 does not count.
3. The next period is April 1, 2002 to Oct. 31, 2002. (on time)

in 1, it toals 30 weeks 3 calendar days. In 2 (period that does not
count totals 30 weeks 4 calendar days).
In 3, we would only use 21 weeks, 4 calendar days to get to 52 weeks.
Using only the on time periods, they complete 52 weeks Aug. 29,
2002. As a result, the next increment first Monday following would
be Sept. 2, 2002.

The fields I have on my spreadsheet so far are:
Cell A8 is the last increment date, A12 is the from date to exclude,
B12 is the to date for period to exclude.
A13 from date to exclude, B13 to date to exclude, A14 from date to
exclude, B14 to date to exlcude, A15 from date to exlcude, B15 to date
to exclude.

Result date goes into Cell C20.

I am thinking I have to convert to days for this calc but am not sure
how to total weeks and days to get to 52 weeks.

I also have to do this calculation for anniversary dates as well
(being able to include in the calc period that do not count toward the
date calc).
thx

 

[Don Guillett Excel MVP]

I have a spreadsheet that looks at the last date an employee was given
an increment.  They are due a new increment 52 weeks from that date,
first Monday following.  Calculating this is easy.   What I have to
now include is periods that do not count.

ie. Last increment date is April 2, 2001
1. They work from April 2, 2001 to Oct. 31, 2001. (on time)
2. the period from Nov. 1, 2001 to March 31, 2002 does not count.
3. The next period is April 1, 2002 to Oct. 31, 2002. (on time)

in 1, it toals 30 weeks 3 calendar days.  In 2 (period that does not
count totals 30 weeks 4 calendar days).
In 3, we would only use 21 weeks, 4 calendar days to get to 52 weeks.
Using only the on time periods, they complete 52 weeks Aug. 29,
2002.    As a result, the next increment first Monday following would
be Sept. 2, 2002.

The fields I have on my spreadsheet so far are:
Cell A8 is the last increment date, A12 is the from date to exclude,
B12 is the to date for period to exclude.
A13 from date to exclude, B13 to date to exclude, A14 from date to
exclude, B14 to date to exlcude, A15 from date to exlcude, B15 to date
to exclude.

Result date goes into Cell C20.

I am thinking I have to convert to days for this calc but am not sure
how to total weeks and days to get to 52 weeks.

I also have to do this calculation for anniversary dates as well
(being able to include in the calc period that do not count toward the
date calc).
thx

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."