Next Cycle Count Date

[Thomas Kuriakose]

Respected Sirs,

Based on the post "Help with finding certain dates" solution by Eibi Sir and John Sir, I have copied the data to capture count cycle to be carried out on every Monday fortnightly.

Except for the 1st Monday for Months February and May, the formulas are giving correct result for all Mondays.

Formula for 1st Monday =A6+9-WEEKDAY(A6) for all months except Feb and May

Kindly let me know why this did not work for Feb and May.

Secondly can we have a conditional format for the dates highlighted in green. The cycle count happens every 14 days and on a Monday only.

Thank you very much,

with regards,
thomas

 

[Chihiro]

The formula will not work if Monday falls on first 2 days of the month, it will be the Monday following.

Use this instead.
=A6+MOD(8-WEEKDAY(A6,2),7)

CF
=MOD(B6-$B$2,14)=0

 

[NARAYANK991]

Hi ,

It would be far better to actually understand the logic of why it does not work.

1. If A6 has a date ( any date ) , then the formula :

=A6 - WEEKDAY(A6)

will always return the earlier Saturday.

Since Monday is 2 days away from Saturday , adding 2 will always yield a Monday. Thus the formula :

=A6 - WEEKDAY(A6) + 2

will always return the earlier Monday.

The problem is that this Monday may not be the first Monday of the month of interest.

Adding 7 does not solve the problem , which is why the formula :

=A6 - WEEKDAY(A6) + 9

is not correct.

The correct formula is just a matter of implementing the logic.

Using the WEEKDAY function with a second parameter of 3 eliminates the need to add 2 ; thus , the formula :

=A6 - WEEKDAY(A6,3)

will always return the earlier Monday.

To get the first Monday of the month , use an IF function , as follows :

=A6-WEEKDAY(A6,3)+IF(MONTH(A6)<>MONTH(A6-WEEKDAY(A6,3)),7,0)

This will always give the first Monday of the month.

Narayan

 

[Chihiro]

Here's explanation of the formula provided.
=A6+MOD(8-WEEKDAY(A6,2),7)

WEEKDAY(A6,2) portion specifies that week starts on Monday (1).

MOD(8-WEEDKAY(A6,2),7) portion subtracts weekday number (1~7) from 8 and divide it by 7, and returns remainder.

So it will divide 1~7 by 7, remainder will be 0~6.

For example if first day is Monday, it will be (8-1)/7, and remainder is 0.

To see how it works, you can separate out MOD() portion into another column and see how number changes.

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the detailed explanation and solution.

Thanks,

with regards,
thomas