Senario: The first working day of any year is the first Monday in the year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the wood
for the trees. Can anybody help please?

Sure, I can help you with that! Here's a formula that will return the date of the first working day of the year based on the scenario you described:

1. `=IF(WEEKDAY(DATE(YEAR(A1),1,1))=2,DATE(YEAR(A1),1 ,8),DATE(YEAR(A1),1,1+MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7)))`

Let me break it down for you:

- WEEKDAY(DATE(YEAR(A1),1,1)) returns the day of the week (as a number, where 1 is Sunday and 7 is Saturday) for January 1st of the year entered in cell A1.
- If January 1st is a Monday (i.e. the result of the above formula is 2), then the first working day is January 8th. This is handled by the first part of the IF statement: IF(WEEKDAY(DATE(YEAR(A1),1,1))=2,DATE(YEAR(A1),1,8 ),...)
- If January 1st is not a Monday, then we need to calculate the date of the first Monday. This is done using the second part of the IF statement: ...DATE(YEAR(A1),1,1+MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7)))
- MOD(9-WEEKDAY(DATE(YEAR(A1),1,1)),7) calculates the number of days between January 1st and the next Monday (i.e. how many days we need to add to January 1st to get to the first Monday). The MOD function ensures that we get a number between 0 and 6 (inclusive), since we only need to add up to 6 days to get to the next Monday.
- Finally, we add the number of days calculated above to January 1st to get the date of the first working day of the year.

Let me know if you have any questions or if there's anything else I can do for you.

You might try:-

=IF(IF(WEEKDAY(DATE(YEAR(A1),1,1),3)0,7-WEEKDAY(DATE(YEAR(A1),1,1),3),0)+DATE(YEAR(A1),1,1 )=DATE(YEAR(A1),1,1),DATE(YEAR(A1),1,DAY(A1)+7),DA TE(YEAR(A1),1,1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),1,1) ),1,0,0,0,0,0,2))

And I suspect it must be dooable a bit neater than this mess.

Mike

"Ron@Buy" wrote:

Senario: The first working day of any year is the first Monday in the year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the wood
for the trees. Can anybody help please?

Another go, found a glitch

=IF(IF(WEEKDAY(DATE(YEAR(A1),1,1),3)0,7-WEEKDAY(DATE(YEAR(A1),1,1),3),0)+DATE(YEAR(A1),1,1 )=DATE(YEAR(A1),1,1),DATE(YEAR(A1),1,DAY(A1)+7),IF (WEEKDAY(DATE(YEAR(A1),1,1),3)0,7-WEEKDAY(DATE(YEAR(A1),1,1),3),0)+DATE(YEAR(A1),1,1 ))

Mike

"Ron@Buy" wrote:

Senario: The first working day of any year is the first Monday in the year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the wood
for the trees. Can anybody help please?

"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Try this:
=DATE(YEAR(A1),1,7-WEEKDAY(DATE(YEAR(A1),1,1),3)+1)

Hi

With Analysis Toolpack Add-In activated
=WORKDAY(DATE(YEAR(A1),1,0),1,DATE(YEAR(A1),1,1))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

When I said simpler, I didn't think that much simpler!! Excellent.

"Arvi Laanemets" wrote:

Hi

With Analysis Toolpack Add-In activated
=WORKDAY(DATE(YEAR(A1),1,0),1,DATE(YEAR(A1),1,1))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Mike
Thanks for your response - your effort was much shorter than my attemps but
unfortunately like mine produced an incorrect result (should return first
date of first Monday of the year, except Jan 1) when different dates are
entered.
Arvi
Thanks for your response - much shorter than Mike's but again unfortunately
didn't return date of first Monday when different dates are entered.
Stephen
Thanks for your response - BRILLIANT, works perfectly every time - I thought
there ought to be a genius out there somewhere.


"Mike H" wrote:

When I said simpler, I didn't think that much simpler!! Excellent.

"Arvi Laanemets" wrote:

Hi

With Analysis Toolpack Add-In activated
=WORKDAY(DATE(YEAR(A1),1,0),1,DATE(YEAR(A1),1,1))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Stephen
Thanks for your response - BRILLIANT, works every time.
When I look at my efforts, I clearly have a lot to learn
Again many thanks,
Regards
Ron

"Stephen" wrote:

"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Try this:
=DATE(YEAR(A1),1,7-WEEKDAY(DATE(YEAR(A1),1,1),3)+1)

Ron@Buy,

Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.

The solution you choose is of course a matter for yourself but 1/1/2007 was
a Monday so according to your logic above for any date in 2007 (or 2001)
entered into A1 the formulas hould return 8/1/ of that year

Avri's excellent and compact formula; which is far superior to my unwieldy
one both return this the other sadly does not.

Mike


"Ron@Buy" wrote:

Mike
Thanks for your response - your effort was much shorter than my attemps but
unfortunately like mine produced an incorrect result (should return first
date of first Monday of the year, except Jan 1) when different dates are
entered.
Arvi
Thanks for your response - much shorter than Mike's but again unfortunately
didn't return date of first Monday when different dates are entered.
Stephen
Thanks for your response - BRILLIANT, works perfectly every time - I thought
there ought to be a genius out there somewhere.


"Mike H" wrote:

When I said simpler, I didn't think that much simpler!! Excellent.

"Arvi Laanemets" wrote:

Hi

With Analysis Toolpack Add-In activated
=WORKDAY(DATE(YEAR(A1),1,0),1,DATE(YEAR(A1),1,1))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Mike
Again I thank you for your efforts, but just to clarify what happened; your
formula whilst a great deal simpler and shorter than my own efforts and came
a lot closer to my desired results didn't quite give me the desired answer, I
want to enter any date in A1 e.g with your solution when I entered, for
instance, 04/05/2007 into A1, A2 returned 11/01/2007 whereas Stephen's
solution returned 08/01/2007, the first working Monday after 1st January.
Nevertheless I am still truely grateful for your time and efforts.
Regards
Ron



"Mike H" wrote:

Ron@Buy,

Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.

The solution you choose is of course a matter for yourself but 1/1/2007 was
a Monday so according to your logic above for any date in 2007 (or 2001)
entered into A1 the formulas hould return 8/1/ of that year

Avri's excellent and compact formula; which is far superior to my unwieldy
one both return this the other sadly does not.

Mike


"Ron@Buy" wrote:

Mike
Thanks for your response - your effort was much shorter than my attemps but
unfortunately like mine produced an incorrect result (should return first
date of first Monday of the year, except Jan 1) when different dates are
entered.
Arvi
Thanks for your response - much shorter than Mike's but again unfortunately
didn't return date of first Monday when different dates are entered.
Stephen
Thanks for your response - BRILLIANT, works perfectly every time - I thought
there ought to be a genius out there somewhere.


"Mike H" wrote:

When I said simpler, I didn't think that much simpler!! Excellent.

"Arvi Laanemets" wrote:

Hi

With Analysis Toolpack Add-In activated
=WORKDAY(DATE(YEAR(A1),1,0),1,DATE(YEAR(A1),1,1))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Hi

Sorry, I didn't read carefully enough. It looks as you want the week with
1'st January in it off.
=DATE(YEAR(A1),1,9-WEEKDAY(DATE(YEAR(D1),1,1),2))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ron@Buy" wrote in message
...
Stephen
Thanks for your response - BRILLIANT, works every time.
When I look at my efforts, I clearly have a lot to learn
Again many thanks,
Regards
Ron

"Stephen" wrote:

"Ron@Buy" wrote in message
...
Senario: The first working day of any year is the first Monday in the
year,
except where the 1st January is a Monday then the 8th January is the
first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns
the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a
correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the
wood
for the trees. Can anybody help please?

Try this:
=DATE(YEAR(A1),1,7-WEEKDAY(DATE(YEAR(A1),1,1),3)+1)

On Wed, 21 Nov 2007 00:59:00 -0800, Ron@Buy
wrote:

Senario: The first working day of any year is the first Monday in the year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the wood
for the trees. Can anybody help please?


I understand you to mean NOT the first working day of the year but rather the
first working MONDAY of the year. Therefo

IF the date in A1 will always be a date in the month of January, then:

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+5)+7+7*(WEEKDAY(A1-DAY(A1))=1)

If it might be any date in the year, then:

=DATE(YEAR(A1),1,0)-WEEKDAY(DATE(YEAR(A1),1,0)+5)+7+7*(WEEKDAY(DATE(YE AR(A1),1,0))=1)


--ron

On Thu, 22 Nov 2007 07:43:07 -0500, Ron Rosenfeld
wrote:

On Wed, 21 Nov 2007 00:59:00 -0800, Ron@Buy
wrote:

Senario: The first working day of any year is the first Monday in the year,
except where the 1st January is a Monday then the 8th January is the first
working day.
Action: When any date is entered into A1 a formula in cell A2 returns the
date of the first working day of that year.
Problem: I'm having difficulty developing a formula that produces a correct
result whatever date is entered into A1.
Plea: I'm sure there must be a simple solution, but I've can't see the wood
for the trees. Can anybody help please?


I understand you to mean NOT the first working day of the year but rather the
first working MONDAY of the year. Therefo

IF the date in A1 will always be a date in the month of January, then:

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+5)+7+7*(WEEKDAY(A1-DAY(A1))=1)

If it might be any date in the year, then:

=DATE(YEAR(A1),1,0)-WEEKDAY(DATE(YEAR(A1),1,0)+5)+7+7*(WEEKDAY(DATE(YE AR(A1),1,0))=1)


--ron


Never mind. Stephen's is shorter.
--ron