Calculation Problem

[zaska]

Hi all,

I have to give a report as follows.

A Society has five cultural clubs ...which are

1. Dance 2. Painting 3. Literature 4. Martial Arts 5. Music

The club meetings are held as follows

Dance - Every 2nd Day

Painting - Every 3rd Day

Literature - Every 4th Day

Martial Arts - Every 5th Day

Music - Every 6th Day

Now i need to find out all the days starting from 01-01-2009 on which no club meetings were held.


Regards,

zaska

 

[jasonb75]

Meaning over a 60 day period there would be

30 dance, 20 painting, 15 literature, 12 martial arts & 10 music meetings?

What happens if a rostered meeting day clashes with a religious festival or public holiday, or even with another meeting?

 

[zaska]

Such Public holidays are not included


Thanks for the reply

 

[jasonb75]

Such Public holidays are not included


Thanks for the reply

Which would mean what?

Does the whole calendar shift so that those meetings happen the next day?

You would need to allow such days in any calculation, excel doesn't know when they are so they would otherwise be counted as normal days.

You also need a starting point for each meeting, i.e. the first date for each meeting type on or after 1/1/09

 

[zaska]

Hi,

I mentioned the starting point i.e 01-01-2009 and i meant the whole calender shift and public holidays or religious holidays are not accounted for in calculation.

The meetings are scheduled as per the pre-determined days irrespective of any day being a holiday.

I want to know the No. of days as well the particulars of such days on which no single meeting of any club is held.

Thanks

 

[MickG]

Try this in a new sheet:-

[COLOR="Navy"]Sub[/COLOR] MG11Jun56
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] Date, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim Ray(1 To rows.Count)
[COLOR="Navy"]For[/COLOR] n = "1/1/2009" To Date
    c = c + 1
    Cells(c, 1) = n
    [COLOR="Navy"]If[/COLOR] c Mod 2 = 0 [COLOR="Navy"]Then[/COLOR] Cells(c, 2) = "Dance"
    [COLOR="Navy"]If[/COLOR] c Mod 3 = 0 [COLOR="Navy"]Then[/COLOR] Cells(c, 3) = "Paint"
    [COLOR="Navy"]If[/COLOR] c Mod 4 = 0 [COLOR="Navy"]Then[/COLOR] Cells(c, 4) = "Lit"
    [COLOR="Navy"]If[/COLOR] c Mod 5 = 0 [COLOR="Navy"]Then[/COLOR] Cells(c, 5) = "M/A"
    [COLOR="Navy"]If[/COLOR] c Mod 6 = 0 [COLOR="Navy"]Then[/COLOR] Cells(c, 6) = "Music"
[COLOR="Navy"]Next[/COLOR] n
c = 0
[COLOR="Navy"]For[/COLOR] n = "1/1/2009" To Date
    c = c + 1
    [COLOR="Navy"]If[/COLOR] Application.CountA(Cells(c, "B").Resize(, 4)) = 0 [COLOR="Navy"]Then[/COLOR]
        p = p + 1
        Ray(p) = n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Range("G1").Resize(p) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zaska]

Thank you for a perfect solution Mick.

Is it possible to Exclude holidays like Christmas, if holidays were entered in a predetermined range.

Anyways you gave me the solution and i am thankful for it.


Regards,

Z

 

[chuckles1066]

That's quite nifty but it errors with a type mismatch?

(Office 2010)

 

[zaska]

Sir,

I didn't understand the following words in your code.

Redim , Ray and Mod.

Could you kindly explain

Thank you

 

[jasonb75]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 96px"><COL style="WIDTH: 54px"><COL style="WIDTH: 58px"><COL style="WIDTH: 68px"><COL style="WIDTH: 79px"><COL style="WIDTH: 43px"><COL style="WIDTH: 98px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD>Public Holiday</TD><TD>Dance</TD><TD>Painting</TD><TD>Literature</TD><TD>Martial Arts</TD><TD>Music</TD><TD>Meetings Held</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/01/2009</TD><TD>Y</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">02/01/2009</TD><TD></TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD>*</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">03/01/2009</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>No meetings</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">04/01/2009</TD><TD></TD><TD>*</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">05/01/2009</TD><TD></TD><TD></TD><TD>*</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">06/01/2009</TD><TD></TD><TD>*</TD><TD></TD><TD>*</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">07/01/2009</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>*</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">08/01/2009</TD><TD></TD><TD>*</TD><TD>*</TD><TD></TD><TD></TD><TD>*</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2:G2</TD><TD>=IF(MOD(ROWS(C$2:C2)-COUNTA($B$2:$B2),COLUMNS($C2:C2)+1)-1,"","*")</TD></TR><TR><TD>H2</TD><TD>=IF(AND(B2="",COUNTIF(C2:G2,"~*")=0),"No meetings","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Fill A2 and C2:H2 down to row 893 and flag public holidays in column B