A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula to Return the next 1st Tuesday of a Month



 
 
Thread Tools Display Modes
  #41  
Old December 3rd, 2007, 02:04 AM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
external usenet poster
 
Posts: 289
Default 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  
Old December 3rd, 2007, 07:43 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default 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  
Old December 3rd, 2007, 09:22 AM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

Thanks Harlan

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.