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
