If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
This formula will give next first Tuesday at noon, based on a cutoff of noon
on the 1st Tuesday =FLOOR(EOMONTH(FLOOR(A1+3.5,7)-4,0)+4,7)+3.5 similarly this will give last Thursday at noon also based on a cutoff at noon on last Thursday =FLOOR(EOMONTH(FLOOR(A1+1.5,7)+5,0)+2,7)-1.5 Note: only works correctly with 1900 date system "Harlan Grove" wrote: Sean wrote... Guys a twist on my OP, how could I return a similar value, but this time the "last" Thursday of each month? Last Thursday of the month for the date in cell A3 is given by =A3-WEEKDAY(A3-4,2)+7*(MATCH(TRUE,DAY(A3-WEEKDAY(A3-4,2) +7*{2;3;4;5;6})8,0)) Make similar adjustments as in my previous response to use noon as the cutoff time on that day. Note: A3-WEEKDAY(A3-n,2) is the previous n_th day of the week before the date in cell A3, where n_th is in the same sense as WEEKDAY(.,2), i.e., 1=Monday, 2=Tuesday, etc. Then note that there are at most 5 of any given weekday in any given month. |
#42
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
daddylonglegs wrote...
This formula will give next first Tuesday at noon, based on a cutoff of noon on the 1st Tuesday =FLOOR(EOMONTH(FLOOR(A1+3.5,7)-4,0)+4,7)+3.5 similarly this will give last Thursday at noon also based on a cutoff at noon on last Thursday =FLOOR(EOMONTH(FLOOR(A1+1.5,7)+5,0)+2,7)-1.5 Note: only works correctly with 1900 date system .... Note also that in Excel 2003 and prior it requires loading the Analysis ToolPak add-in, since EOMONTH isn't a built-in function in those older versions. |
#43
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
Thanks Harlan
|
Thread Tools | |
Display Modes | |
|
|