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

Look he

http://www.cpearson.com/excel/DateTimeWS.htm

heading "Nth day of month and year"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Nikhil" wrote in message
...
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

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

"Jacob Skaria" wrote:

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Try this...

=IF(FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)=A1,"FIRST
SATURDAY",IF(FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)= A1,"SECOND SATURDAY",""))

--------------------
(MS-Exl-Learner)
--------------------

"Nikhil" wrote:

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

"Jacob Skaria" wrote:

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Try the below

=IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7),"Secon d
Saturday",IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),28), 7),"Fourth Saturday",""))

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


"Nikhil" wrote:

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

"Jacob Skaria" wrote:

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Thanks that worked!!!

"Jacob Skaria" wrote:

Try the below

=IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7),"Secon d
Saturday",IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),28), 7),"Fourth Saturday",""))

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


"Nikhil" wrote:

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

"Jacob Skaria" wrote:

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

On Thursday, September 17, 2009 at 2:41:01 PM UTC+5:30, Jacob Skaria wrote:
Try the below

=IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7),"Secon d
Saturday",IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),28), 7),"Fourth Saturday",""))

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


"Nikhil" wrote:

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

"Jacob Skaria" wrote:

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

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


"Jacob Skaria" wrote:

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


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


"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

Thank you its worked

Website batdongsanquangninh.vn lÃ* website hÃ*ng đầu vá»� quảng cáo, mua bán bất Ä‘á»™ng sản tại Quảng Ninh, chÃ*nh thức Ä‘Æ°Æ¡c thÃ*nh láº*p vÃ*o năm 2012. LÄ©nh vá»±c hoạt Ä‘á»™ng chÃ*nh của công ty lÃ* quảng cáo, tÆ° vấn, môi giá»›i, mua bán, cho thuê bất Ä‘á»™ng sản.

Trải qua nhiều năm hoạt Ä‘á»™ng chúng tôi đã ná»— lá»±c vượt qua má»�i khó khăn để dần từng bÆ°á»›c thay đổi được phÆ°Æ¡ng thức quảng cáo vÃ* mua bán truyá»�n thống của ngÆ°á»�i dân thÃ*nh phố giúp cho NgÆ°á»�i bán vÃ* NgÆ°á»�i mua rá»… dÃ*ng thá»±c hiện các giao dịch mua bán nhÃ*, đất má»™t cách minh bạch, nhanh chóng. Rút kinh nghiệm qua việc hoaÌ£t động nhiều năm vaÌ€ tÆ°Ì€ những ý kiến đóng góp của quý khách, trong thá»�i gian tá»›i chúng tôi sẽ ná»— lá»±c hÆ¡n nÆ°a nhằm phát triển Website có thêm những tÃ*nh năng Æ°u việt, quảng bá rá»™ng khắp hÆ¡n nữa giúp cho các giao dịch được tiến hÃ*nh má»™t cách nhanh chóng.

Ra Ä‘á»�i xuất phát từ trải nghiệm thá»±c tế của chÃ*nh ngÆ°á»�i sáng láº*p ra Công ty vá»›i mong muốn giúp cho cá»™ng đồng có được thông tin minh bạch mang lại giá trị lợi Ã*ch tốt nhất cho khách hÃ*ng. Ä�ây chÃ*nh lÃ* cÆ¡ sở ná»�n tảng để công ty xây dá»±ng phÆ°Æ¡ng châm kinh doanh vá»›i mong muốn mang đến sá»± chuyên nghiệp, uy tÃ*n, niá»�m tin cho khách hÃ*ng.

Các dịch vụ chÃ*nh:

- Ä�ăng tin quảng cáo mua bán nhÃ* đất
- �ăng banner quảng cáo
- Ä�ăng bÃ*i PR quảng bá sản phẩm, dịch vụ bất Ä‘á»™ng sản
- Tư vấn, mua bán bất động sản
- Môi giới bất động sản
- ban nha quảng ninh
- mua dat hạ long
- ban dat quang ninh
- ban dat ha long
M�i thông tin đóng góp, quý khách vui lòng liên hệ

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)?

This seems to work for referencing today's date
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*2)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))

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'

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

To calculate the 2nd and 4th Saturdays of every month in Excel, you can use the following formula:

This formula will return the date of the 2nd Saturday of the month based on the date entered in cell A1. To get the date of the 4th Saturday, you can simply change the "2" in the formula to "4".

To highlight the cells that correspond to the 2nd and 4th Saturdays, you can use conditional formatting. Here are the steps:

1. Select the cells where you want to apply the conditional formatting.
2. Go to the Home tab and click on Conditional Formatting in the Styles group.
3. Select New Rule from the drop-down menu.
4. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
5. In the Format values where this formula is true field, enter the following formula for the 2nd Saturday:
   
   
   Code:

   =WEEKDAY(A1,2)=7

   And for the 4th Saturday:
   
   
   Code:

   =WEEKDAY(A1,2)=7+14

6. Click on the Format button and choose the formatting options you want to apply to the highlighted cells.
7. Click OK to close the Format Cells dialog box.
8. Click OK again to close the New Formatting Rule dialog box.

Now, whenever you enter a date in the first column, the corresponding cell in the second column will be highlighted if it falls on the 2nd or 4th Saturday of the month.