Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 17th 09, 08:45 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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  
Old September 17th 09, 08:52 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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


  #3   Report Post  
Old September 17th 09, 08:58 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
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

  #4   Report Post  
Old September 17th 09, 09:01 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
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

  #5   Report Post  
Old September 17th 09, 09:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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



  #6   Report Post  
Old September 17th 09, 10:10 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
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

  #7   Report Post  
Old September 17th 09, 10:11 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
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

  #8   Report Post  
Old September 17th 09, 11:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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

  #9   Report Post  
Old November 26th 16, 03:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2016
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
  #10   Report Post  
Old November 26th 16, 05:13 PM
Banned
 
First recorded activity by ExcelBanter: Nov 2016
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 hoạt động nhiều năm và 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 :


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 06:35 PM
Find Last Saturday of the Year jlclyde Excel Discussion (Misc queries) 23 March 18th 08 01: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 03:05 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017