Remember Me?

#11
January 9th 18, 04:08 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 3
Find 2nd & 4th saturday for every month

On Thursday, September 17, 2009 at 3:45:01 AM UTC-4, Nikhil wrote:
I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

So what if you just wanted to reference the current month and only see it if it was after the current date without referencing another cell (i.e. If it was the second Saturday and before the second Saturday you would see this month but if it was Sunday or after the second Saturday you would see the next month)?

#12
January 9th 18, 04:13 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 3
Find 2nd & 4th saturday for every month

On Thursday, September 17, 2009 at 8:41:01 AM UTC-4, Jacob Skaria wrote:
Thanks Ron for pointing that out

Nikhil, one more ..

=DATE(YEAR(A1),MONTH(A1),1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))
=DATE(YEAR(A1),MONTH(A1),1+7*4)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))

If this post helps click Yes
---------------
Jacob Skaria

"Ron Rosenfeld" wrote:

On Thu, 17 Sep 2009 00:45:01 -0700, Nikhil
wrote:

I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

Please note that Jacob's formula is dependent on your Date system being the
1900 date system. Some machines, and the default for the Mac's, will use the
1904 date system.

For formulas which will work properly regardless of the date system being used,
try:

=IF(A1=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+1)+15,"2nd Saturday",
IF(A1=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+1)+29,"4th Saturday",""))

or

=IF(WEEKDAY(A1)<7,"",IF(AND(DAY(A1)7,DAY(A1)<15) ,
"2nd Saturday",IF(AND(DAY(A1)21,DAY(A1)<29),"4th Saturday","")))

--ron

This seems to work for referencing the current date
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*2)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
#13
January 9th 18, 04:14 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2018 Posts: 3
Find 2nd & 4th saturday for every month

This seems to work for referencing today's date
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*2)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
#14
August 1st 18, 06:29 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2018 Posts: 1
Find 2nd & 4th saturday for every month

On Thursday, September 17, 2009 at 1:15:01 PM UTC+5:30, Nikhil wrote:
I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

DECLARE @firstday AS DATETIME
DECLARE @lastDay AS DATETIME

--SET @today = @dutydate
SET @today = GETDATE()

SET @firstday = DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0)
SET @lastday = DATEADD(DAY, -1, DATEADD(MONTH, 1, @firstday))

--SELECT @today AS [Today], @firstday AS FirstDayOfMonth, @lastday AS LastDayOfMonth

;WITH MyDates AS
(
SELECT @firstday AS MyDate, DATENAME(DW, @firstday) AS NameOfDay
UNION ALL
SELECT DATEADD(DAY, 1, MyDate) AS MyDate, DATENAME(DW, DATEADD(DAY, 1, MyDate)) AS NameOfDay
FROM MyDates
WHERE DATEADD(DAY, 1, MyDate)<@lastDay
)
SELECT MyDate
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY NameOfDay ORDER BY MyDate) AS RowNo, *
FROM MyDates
) AS T
WHERE (RowNo=2 OR RowNo=4) AND NameOfDay = 'Saturday'
#15
April 9th 21, 05:53 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2021 Posts: 1
Find 2nd & 4th saturday for every month

This is a very good conversation.
I do not consider myself as any kind of expert.
However, I thought that I should share a trick that I discovered.

If A1 is the cell with the date, then I have used the following formula in the conditional formatting:
=and(DOW(A1)=7, mod(CEILING(day(A1)/7),2)=1)

It worked for me. This highlights the second and fourth Saturdays.

Girish Mahajan

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post FrankM Excel Worksheet Functions 11 November 12th 08 06:35 PM jlclyde Excel Discussion (Misc queries) 23 March 18th 08 01:14 PM Chuck M Excel Worksheet Functions 9 May 23rd 07 08:38 PM Michael Ermino Excel Worksheet Functions 4 April 11th 07 02:48 AM Barry Excel Worksheet Functions 8 February 7th 06 03:05 PM

All times are GMT +1. The time now is 09:42 PM.