| Home |
| Search |
| Today's Posts |
|
#34
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
Thanks, Ron.....much appreciated.
"Ron Rosenfeld" wrote in message ... On Sat, 1 Dec 2007 19:55:40 -0500, "Ron Coderre" wrote: I gotta hand it to you "date" guys! I spotted the flaw in your formula around 3 hours ago. I've been trying to come up with a working formula ever since. Here's what I came up with: A1: (a date) A2: (the Weekday to return....1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat) This formula returns the NEXT first Tuesday of the month (the final "3" in the formula is the weekday to find): =MIN(CEILING(A1+1,(A1-DAY(A1)+{1;32})-DAY(A1-DAY(A1)+{1;32})+7 -MOD(6+WEEKDAY((A1-DAY(A1)+{1;32})-DAY(A1-DAY(A1)+{1;32})+1)-A2,7))) Looks good. Guess we'll be "handing it to you"!! --ron |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
| dates and 1st Tuesday of month | Excel Worksheet Functions | |||
| Return mm/dd/yy based on 1st Day of Month | Excel Worksheet Functions | |||
| Calculating first/last Monday, Tuesday, etc. in a given month in E | Excel Worksheet Functions | |||
| Return 1st of the Month | Excel Worksheet Functions | |||
Threaded Mode
