Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
How can I get the cells to populate with the dates of the 1st Tuesday of the
month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
C26 is my start date and I need every first Tuesday/month. Thanks!!
"Jessica" wrote: How can I get the cells to populate with the dates of the 1st Tuesday of the month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
The first Tuesday of the following month must be 28 or 35 days from the
previous month so you can use: =IF(MONTH(C26+28)=MONTH(C26),C26+35,C26+28) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jessica" wrote in message ... C26 is my start date and I need every first Tuesday/month. Thanks!! "Jessica" wrote: How can I get the cells to populate with the dates of the 1st Tuesday of the month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
One way,
first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed -- Regards, Peo Sjoblom "Jessica" wrote in message ... C26 is my start date and I need every first Tuesday/month. Thanks!! "Jessica" wrote: How can I get the cells to populate with the dates of the 1st Tuesday of the month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
How can I get the cells to populate with the dates of the 1st Tuesday
of the month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Assuming C26 contains a date within the year of interest, the following formula gives the date of the first Tuesday in January of that year. Copy it across through the next 11 columns to get the following first Tuesday of the next eleven months. If you want to copy it down the rows instead of across the columns, change both occurrences of the function name COLUMN to the function name ROW... =DATE(YEAR($C$26),COLUMN(A1),1+MOD(10-WEEKDAY(DATE(YEAR($C$26),COLUMN(A1),1)),7)) Obviously, to produce the dates for the first Tuesday in January, we only needed the year of interest... so, if you wanted to change the contents of C26 from a date within the year to just the year, you could do that... just change the two occurences of YEAR($C$26) to simply $C$26. Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
Thanks so much!
"Peo Sjoblom" wrote: One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed -- Regards, Peo Sjoblom "Jessica" wrote in message ... C26 is my start date and I need every first Tuesday/month. Thanks!! "Jessica" wrote: How can I get the cells to populate with the dates of the 1st Tuesday of the month? I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every Tuesday. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
One way,
first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
"Rick Rothstein (MVP - VB)" wrote in
message ... One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick A few keystrokes shorter and robust against inserting a new row 1. =DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5)) -- Biff Microsoft Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
"T. Valko" wrote in message
... "Rick Rothstein (MVP - VB)" wrote in message ... One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick A few keystrokes shorter and robust against inserting a new row 1. =DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5)) -- Biff Microsoft Excel MVP Ooops! Disregard! -- Biff Microsoft Excel MVP |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
"T. Valko" wrote in message
... "T. Valko" wrote in message ... "Rick Rothstein (MVP - VB)" wrote in message ... One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick A few keystrokes shorter and robust against inserting a new row 1. =DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5)) -- Biff Microsoft Excel MVP Ooops! Disregard! Eh, I'm having one of those days! Just can't seem to get my brain in gear. Disregard my disregard. The formula *does* work. In further testing I made a change and didn't make the reference absolute which threw me for a loop! -- Biff Microsoft Excel MVP |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
LOL ... having one of those days, eh Biff?
As for your formula, I think you can shorten it by going back to the ROW function call I used (instead of your ROWS one)... unless I am missing something obvious, I am pretty sure that the only insertions that could possibly hurt the calculated series is an insertion in the middle of the series of dates itself... and, in thinking about it some more, I am not entirely sure an internal insertion hurts it either. Rick "T. Valko" wrote in message ... "T. Valko" wrote in message ... "T. Valko" wrote in message ... "Rick Rothstein (MVP - VB)" wrote in message ... One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick A few keystrokes shorter and robust against inserting a new row 1. =DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5)) -- Biff Microsoft Excel MVP Ooops! Disregard! Eh, I'm having one of those days! Just can't seem to get my brain in gear. Disregard my disregard. The formula *does* work. In further testing I made a change and didn't make the reference absolute which threw me for a loop! -- Biff Microsoft Excel MVP |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dates and 1st Tuesday of month
Yeah, I'm just feeling blah today. No energy or enthusiasm.
Anyhow, if you inserted a new row 1, then: ROW(A1) Becomes ROW(A2) Just my personal preference to (almost) always "hardcode" rows/columns. -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... LOL ... having one of those days, eh Biff? As for your formula, I think you can shorten it by going back to the ROW function call I used (instead of your ROWS one)... unless I am missing something obvious, I am pretty sure that the only insertions that could possibly hurt the calculated series is an insertion in the middle of the series of dates itself... and, in thinking about it some more, I am not entirely sure an internal insertion hurts it either. Rick "T. Valko" wrote in message ... "T. Valko" wrote in message ... "T. Valko" wrote in message ... "Rick Rothstein (MVP - VB)" wrote in message ... One way, first formula, assume you put it in C27 =C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5) will give you the first Tuesday's date of the month and year in C26 assuming you have a real date in C26, so if you have 07/01/07 it will return 07/03/07 then in C28 put =DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5) select C28 and copy down as long as needed You can do this with a single formula if desired. Place this formula =DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7)) in C27 (or any other starting cell for that matter) and copy down. If one wanted to place this first Tuesdays across columns instead of down rows, then replace the ROW function calls with COLUMN function calls instead. Rick A few keystrokes shorter and robust against inserting a new row 1. =DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5)) -- Biff Microsoft Excel MVP Ooops! Disregard! Eh, I'm having one of those days! Just can't seem to get my brain in gear. Disregard my disregard. The formula *does* work. In further testing I made a change and didn't make the reference absolute which threw me for a loop! -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Every 1st, 3rd, 5th Tuesday | Excel Worksheet Functions | |||
Calculating first/last Monday, Tuesday, etc. in a given month in E | Excel Worksheet Functions | |||
Dates - Several Days In a month to month only | Excel Discussion (Misc queries) | |||
dates in one month | Excel Worksheet Functions | |||
dates, 1st of month | Excel Worksheet Functions |