Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 9th 18, 04:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
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)?

  #12   Report Post  
Old January 9th 18, 04:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
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))
  #13   Report Post  
Old January 9th 18, 04:14 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
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))
  #14   Report Post  
Old August 1st 18, 06:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2018
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'
  #15   Report Post  
Old April 9th 21, 05:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2021
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 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:42 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