Excel Calendar/Rota program problem

[tommo_blade]

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.

 

[mikebres]

Hi, maybe this link will help. There is a formula to calculate the number of
Mondays in a period, which can changed to count other days of the week.
There are also links at the bottom of the page ot several other timekeeping
answers.

http://www.cpearson.com/excel/DateTimeWS.htm

 

[tommo_blade]

thanks for the link, the information contained there is really useful
but I do not think it provides me with what I need in this instance,
there is lots of information about computing holidays and working out
rota's where the shift pattern is layed out in the excel spreadsheet
but what I need is slightly different.

e.g. I will not have any shift pattern that includes dates & times etc
hardcoded in the spreadsheet - my sheet will simply contain a start &
end date which will be the "period" for which we want to assess (i.e.
April-June incl all days of the week) and then the "pattern" - which
will be something like 4 days on and 6 days off (but will be totally
configurable, so could equally be 3 days on & 7 days off) - I then
need to overlay this pattern onto my defined period and from that work
out for instance the number of Sundays that this period/pattern would
cover.

I guess I need some function or VB code that will take this "pattern"
and count in that pattern over the "period", so if the start date was
the 1st April 2009 it would count up 4 days noting any Sundays in that
4 day period, then it would count the next 6 days but as these were
leave days it would ignoring Sundays, it would then start the next 4
days again counting any Sundays...... and so on until the end date was
reached.

hope that makes sense.