Hi,
I am stuck with a problem that I need assistance with, I am trying
to configure an excel spreadsheet to return some shift rota
calculations for the following:-

1. The number of hours worked in total.
2. The number of weekdays worked.
3. The number of Saturdays worked.
4. The number of Sundays worked.

So far I have a workbook with 2 worksheets:-

One sheet is a 'shift configurator' - the user can configure what the
shift pattern will look like, from cell drop down lists they can
configure 'n' mornings (6hrs), 'n' days(6.30hrs), 'n' afternoons(7hrs)
and how many 'days off' there will be between the shifts (so 4 cell
drop downs in total).

The 2nd sheet is the calculator, thus far I have defined 2 cells which
when selected pop up a 'calendar' (macro) in which to enter the
'start' (B1) and 'end' (B2) dates - the remaining cells do the
calculations above (1-4), so for the number of sundays worked between
the 2 dates:

=SUM(INT((WEEKDAY(B1-1)+B2-B1)/7))&" Sundays Worked"

This is ok when I am applying this cell function over the whole of the
days between the 2 dates but what I now need to do is apply the shift
pattern configuration onto the date range, so if the user had selected
2 mornings & 2 afternoons with 6 days off until the next shift starts
again - I now need to re-work out how many Sundays this will cover
because obviously some Sundays between the 2 dates will fall when the
user is on leave.

Does anyone know how I can apply this logic to the example for Sunday
working, I can then apply the same methodology for the other
calculations.

thanks, Mark.