Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 44
Default Find 2nd & 4th saturday for every month

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Find 2nd & 4th saturday for every month

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

Code:
=DATE(YEAR(A1),MONTH(A1),1+((2-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2))+7)*1)
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Find 2nd & 4th saturday for every month

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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default Find 2nd & 4th saturday for every month

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default Find 2nd & 4th saturday for every month

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 44
Default Find 2nd & 4th saturday for every month

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 506
Default Find 2nd & 4th saturday for every month

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default Find 2nd & 4th saturday for every month

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 44
Default Find 2nd & 4th saturday for every month

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

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Find 2nd & 4th saturday for every month

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


  #11   Report Post  
Banned
 
Posts: 9
Default

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ệ

Äịa chỉ : Nguyá»…n Văn Cừ - Hạ Long - Quảng NinhÄiện thoại : 0984.690.188Email :
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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)?
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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))
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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))
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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'


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Second Saturday Of The Month FrankM Excel Worksheet Functions 11 November 12th 08 07:35 PM
Find Last Saturday of the Year jlclyde Excel Discussion (Misc queries) 23 March 18th 08 02:14 PM
Help finding the date of the last Saturday of a given month Chuck M Excel Worksheet Functions 9 May 23rd 07 08:38 PM
Find the date of the coming up Saturday given the current date. Michael Ermino Excel Worksheet Functions 4 April 11th 07 02:48 AM
first saturday in a month Barry Excel Worksheet Functions 8 February 7th 06 04:05 PM


All times are GMT +1. The time now is 05:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"