Date calculation for Monday of one month to the Monday of the next

[Sunnyskies]

Morning from a cold SA, sunny but temp is +- 10 deg C

Need to show the dates from the Monday of one month to the Monday of the
next month. This is for a payroll period, thus need to show the dates in
between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that
users can capture the respective hours per employee.

Column B would have the respective dates as per the formula, cell B1 would
have the start date which the data capturer inputs, cell B4 would be the
first Monday of June and then cell B5 would have the Tuesday and then so on
until we reach the 2nd July which is the first monday in the new month,
thereafter the cells should not show any date.

The employees names will be in row 4

Thanks

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Morning from a cold SA, sunny but temp is +- 10 deg C
|
| Need to show the dates from the Monday of one month to the Monday of the
| next month. This is for a payroll period, thus need to show the dates in
| between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that
| users can capture the respective hours per employee.
|
| Column B would have the respective dates as per the formula, cell B1 would
| have the start date which the data capturer inputs, cell B4 would be the
| first Monday of June and then cell B5 would have the Tuesday and then so on
| until we reach the 2nd July which is the first monday in the new month,
| thereafter the cells should not show any date.
|
| The employees names will be in row 4
|
| Thanks

 

[Mike H]

Hi I'm not sure what level of automation you want in this but the simplest
way to do it is have your person put the date (must be 1st of the month) in
B1 and then this formula in B4 which will calculate the first Monday of the
date in B1

=IF(WEEKDAY(B1,3)>0,7-WEEKDAY(B1,3),0)+B1

In B5 you then simply enter =B4+1 and drag down as far as you require.

Mike

 

[Stefi]

I made a search with keywords "first Monday" and got several answers. Try it!

Regards,
Stefi


„Sunnyskies†ezt írta:

 

[Toppers]

Try:

B1=Start of month e.g 01/06/07

B4: =$B$1+(8-WEEKDAY($B$1,2))
B5:
=IF(B4<>"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1,1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1,""),"")

Copy down until blank cell reached

HTH

 

[Sunnyskies]

Almost but how does it stop at the first Monday of the next month?

 

[Sunnyskies]

Excellent.

Because you nailed it and the formula looks exhausting, by the powers
invested by me from me, I suggest you go home early today.

Cheers

 

[Sunnyskies]

Stop, before you go home.

There is a problem with September:

03 September 2007 Monday
04 September 2007 Tuesday
05 September 2007 Wednesday
06 September 2007 Thursday
07 September 2007 Friday
08 September 2007 Saturday
09 September 2007 Sunday
10 September 2007 Monday
11 September 2007 Tuesday
12 September 2007 Wednesday
13 September 2007 Thursday
14 September 2007 Friday
15 September 2007 Saturday
16 September 2007 Sunday
17 September 2007 Monday
18 September 2007 Tuesday
19 September 2007 Wednesday
20 September 2007 Thursday
21 September 2007 Friday
22 September 2007 Saturday
23 September 2007 Sunday
24 September 2007 Monday
25 September 2007 Tuesday
26 September 2007 Wednesday
27 September 2007 Thursday
28 September 2007 Friday
29 September 2007 Saturday
30 September 2007 Sunday
01 October 2007 Monday
02 October 2007 Tuesday
03 October 2007 Wednesday
04 October 2007 Thursday
05 October 2007 Friday
06 October 2007 Saturday
07 October 2007 Sunday
08 October 2007 Monday

It should have stopped at the 1st October.

Thanks

 

[Toppers]

try:

=IF(B4<>"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1,1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4+1,""),"")

 

[Toppers]

or ...

=IF(B4<>"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1,1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1,""),"")

Removes need for MOD

 

[Sunnyskies]

Oops another problem, this time with October. The start date even though
entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4

08 October 2007 Monday
09 October 2007 Tuesday
10 October 2007 Wednesday
11 October 2007 Thursday
12 October 2007 Friday
13 October 2007 Saturday
14 October 2007 Sunday
15 October 2007 Monday
16 October 2007 Tuesday
17 October 2007 Wednesday
18 October 2007 Thursday
19 October 2007 Friday
20 October 2007 Saturday
21 October 2007 Sunday
22 October 2007 Monday
23 October 2007 Tuesday
24 October 2007 Wednesday
25 October 2007 Thursday
26 October 2007 Friday
27 October 2007 Saturday
28 October 2007 Sunday
29 October 2007 Monday
30 October 2007 Tuesday
31 October 2007 Wednesday
01 November 2007 Thursday
02 November 2007 Friday
03 November 2007 Saturday
04 November 2007 Sunday
05 November 2007 Monday

 

[Toppers]

=$B$1+MOD((7-WEEKDAY($B$1,3)),7)

and for consistency, use this for B5 onwards:

=IF(B4<>"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1,1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1,""),"")

 

[Sunnyskies]

Corrected the formula, but then went back to September to test and it still
does not stop at 01 October 2007, it continues to 08 October 2007.

Thanks

 

[Toppers]

My test test for September : first Monday is 03/09/2007 and last Monday is
01/10/2007, using either formulae..

Are the other dates "left" from previous calculations?

 

[Sunnyskies]

Just copied the formulas again, and still get 08 October as the last Monday.

Possible to get your e-mail address so that can send you the file?

Thanks

 

[Toppers]

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM from above.

 

[Sunnyskies]

Gone.

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSPAM from above.

 

[Ron Rosenfeld]

Morning from a cold SA, sunny but temp is +- 10 deg C

Need to show the dates from the Monday of one month to the Monday of the
next month. This is for a payroll period, thus need to show the dates in
between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that
users can capture the respective hours per employee.

Column B would have the respective dates as per the formula, cell B1 would
have the start date which the data capturer inputs, cell B4 would be the
first Monday of June and then cell B5 would have the Tuesday and then so on
until we reach the 2nd July which is the first monday in the new month,
thereafter the cells should not show any date.

The employees names will be in row 4

Thanks

Try this:

B1: User supplied date

If the employees names are in Row 4, how is the first Monday in B4?

In any event, to have the first Monday of the Month supplied by the User in B1,

B4: =$B$1-DAY($B$1)+8-WEEKDAY($B$1-DAY($B$1)+6)

Then,

B5:

=IF($B$4+ROWS($1:1)>$B$4-DAY($B$4)+40-
DAY($B$4-DAY($B$4)+32)-WEEKDAY($B$4-
DAY($B$4)+32-DAY($B$4-DAY($B$4)+32)+6),"",$B$4+ROWS($1:1))

copy/drag down as far as required. It will blank any dates that might be
beyond the 1st Monday of the next month.

One potential issue: If the user supplied date is in the subsequent month, but
prior to the first Monday of that month, then B4 will contain the first Monday
of that subsequent month. If this is an issue, let me know.

--ron

 

[Sunnyskies]

Morning Ron,

Users have changed their minds (how unusual ; ) ). They want from the 1st
Monday of the month to the first Sundy of the next month.

Can you please amend your formula according to the new criteria?

Thanks

 

[Ron Rosenfeld]

Morning Ron,

Users have changed their minds (how unusual ; ) ). They want from the 1st
Monday of the month to the first Sundy of the next month.

Can you please amend your formula according to the new criteria?

Thanks

That's a simple modification. Just change the formula in B5 to the one below,
and copy/drag down as far as required (at least 34 rows.

By the way, what are the dates that the user might enter in B1?

The way the formula is written, B4 will return the first Monday of the month
entered in B1, and not necessarily the starting date of the period that
includes B1.

For example,

B1: 1 Dec 2007
B4: 3 Dec 2007

B1: 30 Nov 2007
B2: 5 Nov 2007

======================================
B5:
=IF($B$4+ROWS($1:1)>$B$4-DAY($B$4)+40-DAY($B$4-
DAY($B$4)+32)-WEEKDAY($B$4-DAY($B$4)+32-DAY(
$B$4-DAY($B$4)+32)),"",$B$4+ROWS($1:1))
--ron