Quick date calculation functions

[Grey]

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

 

[Bob Phillips]

--

HTH

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

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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