Formula for the first working day of the year

[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.
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 H]

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),DATE(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

 

[Mike H]

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

 

[Stephen]

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)

 

[Arvi Laanemets]

Hi

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

 

[Mike H]

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

 

[Ron@Buy]

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.

 

[Ron@Buy]

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

 

[Mike H]

Ron@Buy,

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]

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

 

[Arvi Laanemets]

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

 

[Ron Rosenfeld]

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. Therefore:

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(YEAR(A1),1,0))=1)


--ron

 

[Ron Rosenfeld]

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

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(YEAR(A1),1,0))=1)


--ron

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