excell 2003 Question.

Gadgetman496

Can anyone show me how to achieve the following?

I have an excel invoice that I want to get to multiply a figure in cell E3 by 4 or 5 depending on the number of Thursdays in the month (some have 4 & others 5)

The months this year for example are

Jan = 5 Thur
Feb = 4 Thur
Mar = 4 Thur
Apr = 5 Thur
May = 4 Thur
Jun = 4 Thur
July = 5 Thur
Aug = 4 Thur
Sept = 4 Thur
Oct = 5 Thur
Nov = 4 Thur
Dec = 5 Thur

The figure of 4 or 5 will need to be automatically inserted into Cell A16 depending on which month of the year it is, which I'm hoping can in some way be determined from Cell E6 which is set to show the current date.

Is there any easyish way to get it to do this?

-

Overheal

hey, try these: http://www.cpearson.com/excel/DateTimeWS.htm

in particular:

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

Gadgetman496

Thanks for the reply Overheal but I should have stated that I have little or no knowledge of using functions in excel Over my head for now I'm afraid.

-

rockal

OK Gadget

Cell E6... (Current Date)
Cell F6... 0 (zero)
Cell G6... =EOMONTH(E6,(F6-1))+1
Cell H6... =EOMONTH(E6,F6)
Cell I6... 5
Cell A16...=SUM(IF(WEEKDAY(G6-1+ROW(INDIRECT("1:"&TRUNC(H6-G6)+1)))=I6,1,0))

The formula in Cell A16 must be entered as an array (To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later.

Tools > Add-Ins > Analysis ToolPak must be checked for EOMONTH function.

Also be sure to Date format Cells G6 & H6.
The number 5 in Cell I6 is for Thursday

Gadgetman496

rockal wrote: »

OK Gadget

Cell E6... (Current Date)
Cell F6... 0 (zero)
Cell G6... =EOMONTH(E6,(F6-1))+1
Cell H6... =EOMONTH(E6,F6)
Cell I6... 5
Cell A16...=SUM(IF(WEEKDAY(G6-1+ROW(INDIRECT("1:"&TRUNC(H6-G6)+1)))=I6,1,0))

The formula in Cell A16 must be entered as an array (To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later.

Tools > Add-Ins > Analysis ToolPak must be checked for EOMONTH function.

Also be sure to Date format Cells G6 & H6.
The number 5 in Cell I6 is for Thursday

Ah! rockal,

That's more my limited language

It was so clear that even I could follow it.

I really appreciate you taking the time to post that in such an understandable manor.

I followed it exactly as posted & it worked a treat.

Thank you again,

Gadget.

-

Gadgetman496

If you are still about rockal?

The entry of 4 or 5 in Cell A16 is working fine but I want to reflect the 4 or 5 figure in Cells A17, A18,A19 & A20 too. I thought if I got the formula for one cell I could use the fill series to pull it down through the other cells but that option is grayed out.

Is there much to do to make it display that figure 4 or 5 in the other cells?

Thanks again.

-

rockal

Ah, doing it that way changes the reference cells too.
Simplest way is just put =A16 in A17, A18, etc.