MrExcel Publishing
Your One Stop for Excel Tips & Solutions

First Tuesday of the month - MORE


Posted by Brett on June 22, 2000 6:11 AM

I asked if anyone knew how to generate a series of dates in a column based upon the first Tuesday of the month.

The following was posted by Jaime as a fix:

=DATE(YEAR(K2),MONTH(K2)+1,1) - MOD(DATE(YEAR(K2),MONTH(K2)+1,1)-3,7)+7


Assuming starting date is in cell K2 (does not have to be a tuesday).

Well this ALMOST works. The problem is that if the first Tuesday is the 1st (as in May 1st 2001), the above returns a date of the 8th!

Any ideas?

Brett


Posted by Brett on June 28, 0100 12:07 AM


I don't know what I was doing but the above works fine now.
Thanks
Brett

Posted by Brett on June 28, 0100 12:10 AM

I worked out the problem. In order to get a incrementing date, I needed to add a +1 after each month.
eg:
=DATE(YEAR(K2),MONTH(K2)+1,1)+
MOD(3-WEEKDAY(DATE(YEAR(K2),MONTH(K2)+1,1)),7)
This works fine.
Brett

Posted by Tim Francis-Wright on June 23, 0100 10:10 AM

I asked if anyone knew how to generate a series of dates in a column based upon the first Tuesday of the month.

To get the first Tuesday in a month, assuming that
the date is in K2, I used:

=DATE(YEAR($K$2),MONTH($K$2),1)+
MOD(3-WEEKDAY(DATE(YEAR($K$2),MONTH($K$2),1)),7)

Posted by Brett on June 25, 0100 10:35 AM

I tried to use the fill function on this but it just repeated the same date instead of incrementing each month ala:
5-Sep-00
3-Oct-00
7-Nov-00
5-Dec-00
etc

Removing all the $ didn't make any difference either.

Brett

Posted by Brett on June 25, 0100 10:40 AM

Ok but I get the same problem as above in that it doesn't increment monthly if I fill down the column.
Brett

Posted by mads on June 25, 0100 11:36 AM

It does when I do it!
mads

Posted by mads on June 22, 0100 7:00 PM

I asked if anyone knew how to generate a series of dates in a column based upon the first Tuesday of the month.


=IF(WEEKDAY(K2)=3,K2,(DATE(YEAR(K2),MONTH(K2),1) - MOD(DATE(YEAR(K2),MONTH(K2),1)-3,7)+7))

mads

Posted by mads on June 22, 0100 10:37 PM

Sorry, I think this only works if the date happens to be the first Tuesday.
Back yo the drawing board!
mads

Posted by mads on June 25, 0100 10:53 PM

The following was posted by Jaime as a fix: =DATE(YEAR(K2),MONTH(K2)+1,1) - MOD(DATE(YEAR(K2),MONTH(K2)+1,1)-3,7)+7

It sounds like you have your calculation set to manual instead of automatic. Look at Tools>Options>Calculation.
mads

Posted by mads on June 22, 0100 11:07 PM

I asked if anyone knew how to generate a series of dates in a column based upon the first Tuesday of the month.


=DATE(YEAR(K2),MONTH(K2),1)+IF(3<WEEKDAY(DATE(YEAR(K2),MONTH(K2),1)),7-WEEKDAY(DATE(YEAR(K2),MONTH(K2),1))+3,3-WEEKDAY(DATE(YEAR(K2),MONTH(K2),1)))

mads

Posted by mads on June 22, 0100 11:08 PM

I asked if anyone knew how to generate a series of dates in a column based upon the first Tuesday of the month.


=DATE(YEAR(K2),MONTH(K2),1)+IF(3<WEEKDAY(DATE(YEAR(K2),MONTH(K2),1)),7-WEEKDAY(DATE(YEAR(K2),MONTH(K2),1))+3,3-WEEKDAY(DATE(YEAR(K2),MONTH(K2),1)))

mads

Posted by mads on June 22, 0100 11:11 PM

For some reason, the formula is not being shown properly.
Look at the Comments box below - it shows properly there.
mads