MLK b-day as holiday date
hi,
i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg |
MLK b-day as holiday date
Try this...
=DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) The general formula is: =DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week)) Whe N = the nth weekday (3 for the 3rd Monday) day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday -- Biff Microsoft Excel MVP "Greg" wrote in message ... hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg |
MLK b-day as holiday date
Left out the critical piece of information!
Try this... =DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) A1 holds the year number of interest. For example, 2011. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) The general formula is: =DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week)) Whe N = the nth weekday (3 for the 3rd Monday) day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday -- Biff Microsoft Excel MVP "Greg" wrote in message ... hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg |
MLK b-day as holiday date
Hi,
try this where a1 contains any properly formatted date. =A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),22,21,20,19,18,17,23) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Greg" wrote: hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg |
MLK b-day as holiday date
Biff,
Never seen that method before, very neat but I bet you meant:- =DATE(A1,1,21)-WEEKDAY(DATE(A1,1,6)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "T. Valko" wrote: Try this... =DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) The general formula is: =DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week)) Whe N = the nth weekday (3 for the 3rd Monday) day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday -- Biff Microsoft Excel MVP "Greg" wrote in message ... hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg . |
MLK b-day as holiday date
Biff,
Apologies, i understand your formula now. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Biff, Never seen that method before, very neat but I bet you meant:- =DATE(A1,1,21)-WEEKDAY(DATE(A1,1,6)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "T. Valko" wrote: Try this... =DATE(A1,1,22)-WEEKDAY(DATE(A1,1,6)) The general formula is: =DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-day_of_week)) Whe N = the nth weekday (3 for the 3rd Monday) day_of_week = a number from 1 to 7. 1 = Sunday thru 7 = Saturday -- Biff Microsoft Excel MVP "Greg" wrote in message ... hi, i need a function to calculate what date would be the 3rd monday in January for a given year. can this be done w/o arrays? thanks -- ______ Regards, Greg . |
All times are GMT +1. The time now is 03:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com