Date Question

[AMaleThing]

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.

 

[FSt1]

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

regards
FSt1

 

[Tom Ogilvy]

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

 

[Joel]

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)

 

[Rick Rothstein \(MVP - VB\)]

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]

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








- 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).

 

[Ron Rosenfeld]

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

 

[AMaleThing]

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








- 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?