Need a formula to display date to satisfy these requirements.
If today is beyond first Thursday of the month: 3rd Thursday
If today is beyond third Thursday of the month: 1st Thursday of next month
--
David
Need a formula to display date to satisfy these requirements.
If today is beyond first Thursday of the month: 3rd Thursday
If today is beyond third Thursday of the month: 1st Thursday of next month
--
David
Bob Phillips posted this:
Generic formula
=DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW))
Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day,
Sun=1, Mon=2, etc.
======
Me, personally--I'd use some helper cells instead of creating a giant formula.
First Thursday of this month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))
Third Thursday of this month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))
First Thursday of next month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))
With those values in (say) A1, B1, C1, I'd use this formula:
=if(today()>b1,c1,if(today()>a1,b1,a1))
(good gawd--if you want a single cell formula:
=IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))),
IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))),
(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))))
(just copy and paste any of those multilined formulas into the formula bar)
David wrote:
>
> Need a formula to display date to satisfy these requirements.
> If today is beyond first Thursday of the month: 3rd Thursday
> If today is beyond third Thursday of the month: 1st Thursday of next month
>
> --
> David
--
Dave Peterson
I can get there with a lookup table and a helper column.
The table converts the weekday of the last day of last month to the day of
this month's first Thursday. The first row: 1,2,3,4,5,6,7. The second row:
4,3,2,1,7,6,5. Put that in Sheet2!A1:G2.
In A10:
=DATE(YEAR(TODAY()),MONTH(TODAY()),HLOOKUP(WEEKDAY(TODAY()-DAY(TODAY())),Sheet2!$A$1:$G$2,2,FALSE))
In B10:
=IF(TODAY()<=A10,A10,IF(TODAY()<=A10+14,A10+14,IF(DAY(A10+28)>28,A10+28,A10+35)))
The first formula figures out the date of the first Thursday of this month.
The second does the calculation you described: if we're on or before the
first Thursday of this month, return the first Thursday. Otherwise, if we're
on or before the third Thursday, return the third Thursday. Otherwise,
advance to the first Thursday of next month.
--Bruce
"David" wrote:
> Need a formula to display date to satisfy these requirements.
> If today is beyond first Thursday of the month: 3rd Thursday
> If today is beyond third Thursday of the month: 1st Thursday of next month
>
> --
> David
>
Hi!
What do want to do if today is less than or equal to the first Thursday of
the month?
The formula to do this would be very long!
Biff
"David" <[email protected]> wrote in message
news:[email protected]...
> Need a formula to display date to satisfy these requirements.
> If today is beyond first Thursday of the month: 3rd Thursday
> If today is beyond third Thursday of the month: 1st Thursday of next month
>
> --
> David
On Fri, 04 Nov 2005 14:35:36 -0800, David <[email protected]> wrote:
>Need a formula to display date to satisfy these requirements.
>If today is beyond first Thursday of the month: 3rd Thursday
>If today is beyond third Thursday of the month: 1st Thursday of next month
Complicated, but doable:
=IF(A1<=(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3)),
A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3),IF(AND(
A1>(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+3)),A1<=(
A1-DAY(A1)+14+8-WEEKDAY(A1-DAY(A1)+3))),A1-DAY(
A1)+14+8-WEEKDAY(A1-DAY(A1)+3),A1-DAY(A1)+40-DAY(
A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+35-DAY(A1-DAY(A1)+32))))
--ron
Dave Peterson wrote
> Bob Phillips posted this:
>
> Generic formula
>
> =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW
> ))
>
> Nth is the instance, 1st,.2nd etc, DoW is the serial number of the
> Day, Sun=1, Mon=2, etc.
>
> ======
> Me, personally--I'd use some helper cells instead of creating a giant
> formula.
>
> First Thursday of this month:
> =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))
>
> Third Thursday of this month:
> =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))
>
> First Thursday of next month:
> =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))
>
> With those values in (say) A1, B1, C1, I'd use this formula:
>
> =if(today()>b1,c1,if(today()>a1,b1,a1))
>
> (good gawd--if you want a single cell formula:
>
> =IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
> (DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1)
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))),
> IF(TODAY()>(DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))),
> (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))),
> (DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1)
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))))
>
> (just copy and paste any of those multilined formulas into the formula
> bar)
>
>
>
>
> David wrote:
>>
>> Need a formula to display date to satisfy these requirements.
>> If today is beyond first Thursday of the month: 3rd Thursday
>> If today is beyond third Thursday of the month: 1st Thursday of next
>> month
>>
>> --
>> David
>
Good gawd is right!! Especially since I want to surround the result with
some text!! I think I'll just type in what I need twice a month.
Thanks to all who took the time and trouble to present solutions.
--
David
Biff wrote
> What do want to do if today is less than or equal to the first
> Thursday of the month?
>
> The formula to do this would be very long!
It should default to first Thursday of current month.
--
David
Dave Peterson wrote
> Me, personally--I'd use some helper cells instead of creating a giant
> formula.
Hmm... Curiously, after experimenting by adjusting computer clock/calendar,
I couldn't advance result past Dec 1st.
--
David
David wrote
> Hmm... Curiously, after experimenting by adjusting computer
> clock/calendar, I couldn't advance result past Dec 1st.
Correction. Tests will go as far as Dec 15. If I set date to Dec 15 or
later, result reverts to Dec 1. Doesn't want to go past Dec 15 or into Jan,
2006.
--
David
Dave Peterson wrote
> With those values in (say) A1, B1, C1, I'd use this formula:
Setting system date to Dec 4
A1: 11/3/2005
B1: 12/15/2005
C1: 12/1/2005
--
David
Dave Peterson wrote
> =DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3
> -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5)))
Ok, changed other two formulas to match this syntax, i.e., placement of
parentheses, and it works.
p.s. Sorry about not changing date back to today before sending prior post.
--
David
Hi David:
This is pretty generic, but it works.
1. Go through the calendar and write down all the Thursday’s that meet your criteria.
2. Place them in order and in a column somewhere off the visible area of the work sheet such as column AA.
=IF($A$1<=AA1,AA1,"")
3. Place this formula in AB1 and copy it down to match the range of dates in the AA column, cell $A$1 is whatever cell you keep today’s date in.
=MIN(AB1:AB27)
4. Place this formula in whatever cell you choose to see the results and format that cell to date.
Matt
Flintstone wrote
>
> Hi David:
>
> This is pretty generic, but it works.
>
> 1. Go through the calendar and write down all the Thursday’s that meet
> your criteria.
>
> 2. Place them in order and in a column somewhere off the visible area
> of the work sheet such as column AA.
>
> =IF($A$1<=AA1,AA1,"")
>
> 3. Place this formula in AB1 and copy it down to match the range of
> dates in the AA column, cell $A$1 is whatever cell you keep today’s
> date in.
>
> =MIN(AB1:AB27)
>
> 4. Place this formula in whatever cell you choose to see the results
> and format that cell to date.
>
>
>
> Matt
>
>
Thanks for jumping in. I want this to be perpetual and maintenance free, so
I decided to go with Dave Petersen's 'helper cell' formulas.
--
David
So how would I change this formula to reflect the 2nd and 4th Thursdays?
=IF(B8<=(B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3)),B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3),IF(AND(B8>(B8-DAY(B8)+8-WEEKDAY(B8-DAY(B8)+3)),B8<=(B8-DAY(B8)+14+8-WEEKDAY(B8-DAY(B8)+3))),B8-DAY(B8)+14+8-WEEKDAY(B8-DAY(B8)+3),B8-DAY(B8)+40-DAY(B8-DAY(B8)+32)-WEEKDAY(B8-DAY(B8)+35-DAY(B8-DAY(B8)+32))))
gtluv01 welcome to the forum
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I was able to get the formula to work, How do I make sure as time goes on that my dates won't get messed up? Is there a place in the formula that I can put in the Year and the month? I am worried that because it is based on today's date that the dates will move as the months move.
This is the formula I ended up using
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*1) -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-5))
for next month I used
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1+7*1) -WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-5))
Hello hjbinn01,
Please start your own thread with your specific query - you can link to this thread if it's relevant
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks