Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

hi
see this site....
http://www.cpearson.com/Excel/datetime.htm#DaysInMonth

regards
FSt1

"AMaleThing" wrote:

Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

Read through all the formulas on this page: (don't just look at the top of
the page)

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

--
Regards,
Tom Ogilvy



"AMaleThing" wrote:

Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

if A1 contains th efirst date of the month (ie 7/1/08)

=A1+MOD(8-WEEKDAY(A1,2),7)

if you need to create the 1st day of the month then add DATE

=DATE(2008,7,1)+MOD(8-WEEKDAY(DATE(2008,7,1),2),7)

"AMaleThing" wrote:

Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

If A1 contains any date within the month, then the first Monday of that
month can be found with this formula....

=A1-DAY(A1)+1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),1,0,6,5,4,3,2)

Rick


"AMaleThing" wrote in message
...
Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

On 4 Jul, 16:19, "Rick Rothstein \(MVP - VB\)"
wrote:
If A1 contains any date within the month, then the first Monday of that
month can be found with this formula....

=A1-DAY(A1)+1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),1,0,6,5,4,3,2)

Rick

"AMaleThing" wrote in message

...



Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.- Hide quoted text -

- Show quoted text -

Thank you for your solutions. I'd have never have used that script
even after spending the best part of an hour looking in the index
help.

Thanks to everyone. (links appreciated).

On Fri, 4 Jul 2008 06:53:39 -0700 (PDT), AMaleThing wrote:

Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.

With some date in A1:

First Monday:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)-1)

And, requiring either Excel 2007 or installation of the Analysis ToolPak for
earlier versions of Excel:

first working day of the week:

=WORKDAY(A1-WEEKDAY(A1),1,Holidays)

where Holidays is a range where your holiday dates are stored.

First working day of the month:

=WORKDAY(A1-DAY(A1),1)
--ron

On 4 Jul, 16:19, "Rick Rothstein \(MVP - VB\)"
wrote:
If A1 contains any date within the month, then the first Monday of that
month can be found with this formula....

=A1-DAY(A1)+1+CHOOSE(WEEKDAY(A1-DAY(A1)+1),1,0,6,5,4,3,2)

Rick

"AMaleThing" wrote in message

...



Is there a forumla to enter into a cell which can report for e.g.

a) the date of the first monday of the month?
b) the first working day of the week?

Much appreciated.- Hide quoted text -

- Show quoted text -

Starting with the simpiest looking formulas, I'm trying:
=A1+MOD(8-WEEKDAY(A1,2),7)

Can you help me to understand why 8 is subtracted from WEEKDAY?