Quick date calculation functions

If I have a cell with the number of a month and a year, how can I produce a
formula which will...

1. Return the number of days in that month.

2. Calculate the number of specific days of the week in that month (e.g. how
many Tuesdays in Feb 2006)

If I have two dates, how can I calculate how many working days (mon-fri)
between them?

Thanks in advance,

Graham

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Grey" <[email protected]> wrote in message
news:[email protected]...
> If I have a cell with the number of a month and a year, how can I produce
a
> formula which will...
>
> 1. Return the number of days in that month.

=DAY(DATE(A1,B1+1,0))

>
> 2. Calculate the number of specific days of the week in that month (e.g.
how
> many Tuesdays in Feb 2006)

=INT((DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,2))
)+6)/7)

where the 6 refers to the day number, 1 = SUn, etc


> If I have two dates, how can I calculate how many working days (mon-fri)
> between them?

=NETWORKDAYS(A1,A2,holidays) where holidays is a list of holiday dates

On Sun, 13 Mar 2005 19:29:39 -0000, "Grey" <[email protected]> wrote:

>If I have a cell with the number of a month and a year, how can I produce a
>formula which will...
>
>1. Return the number of days in that month.
>

=32-DAY(A1-DAY(A1)+32)

>
>2. Calculate the number of specific days of the week in that month (e.g. how
>many Tuesdays in Feb 2006)
>

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+2))+35)>7)

will give the number of Tuesdays in a month.

For a different weekday, change the '2' near the end accordingly:

1:Monday 7:Sunday

Thanks to Daniel M.




>If I have two dates, how can I calculate how many working days (mon-fri)
>between them?
>

=NETWORKDAYS(StartDate,EndDate).

Note that the count will include both the StartDate and the EndDate

Also note (see HELP) that there is an optional Holidays parameter which enables
you to include Holidays in the range. You do have to set up a list of
Holidays, though.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.



>Thanks in advance,
>
>Graham
>

--ron

On Sun, 13 Mar 2005 16:04:52 -0500, Ron Rosenfeld <[email protected]>
wrote:

>>If I have a cell with the number of a month and a year, how can I produce a
>>formula which will...
>>
>>1. Return the number of days in that month.
>>
>
>=32-DAY(A1-DAY(A1)+32)
>
>>

Oops, that formula assumes some full date is in A1.

If you have the number of the month in A1, and the year in B1, then the formula
would be:

=DAY(DATE(B1,A1+1,0)) (as per Bob)


--ron

Originally Posted by Ron Rosenfeld

On Sun, 13 Mar 2005 19:29:39 -0000, "Grey" <[email protected]> wrote:
[color=blue]

=4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+2))+35)>7)

will give the number of Tuesdays in a month.

For a different weekday, change the '2' near the end accordingly:

1:Monday 7:Sunday

Thanks to Daniel M.


--ron

Can anybody please assist on how I can include a start and end date for this function.
ie I have set up a count of each specific day per month for Feb2010 to December 2011.
How do I then implement a specific date into this eg I only want the count of each specific day in each month starting at the 13th Feb, and ending at 22nd December.

Also, I have just added together the 7 different functions for days 1 through to 7, creating a formula that takes up 5 lines in the formula bar. Is there a basic way for me to do a "For days 1:7, do xxxxxxxxxxx

Thanks
Darren