Excel Formula - Second or Third Friday 1

[Jontmke]

I have a report from an application that shows schedules such as this:

 Monthly, On 2nd Fri At 18:30.

Can anyone think of a way to get an Excel formula to read this? What would be ideal is a type of calendar display where I could choose a day and see what jobs are scheduled.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[Crystalyzer]

I'm confused. Is the text "Monthly, On 2nd Fri At 18:30" the parameter for showing the schedule of jobs or is it just the title of the report?

When you say you need an excel formula to "read" this do you really mean you'd like to have a calendar in excel that can produce the text above from the selected date?



Thanks and best regards,
-Lloyd

 

[SkipVought]

Hi,

The REMAINDER (MOD) any date divided by 7 on a FRIDAY is always 6...
[tt]
=Mod(YourDate,7)
[/tt]
The second Friday will always be between the 14^th and 20^th, right?

So make a list of dates for the year in Col A Named Date

Col B Named Sw
[tt]
=IF(AND(DAY(A2)>=14,DAY(A2)<20),IF(MOD(A2,7)=6,1,0),0)
[/tt]
[tt]
D1:=date(year(today()),month(today()),1)
E1:=date(year(today()),month(today())+1,0)
F1:=SUMPRODUCT((Date>=D1)*(Date<=E1)*(Sw)*(Date))
[/tt]
F1 contains the current month's second Firday



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[pbrodsky]

The 2nd friday would be btwn the 8th if the 1st friday is on the 1st) and the 14th, actually.

 

[SkipVought]

Here's a formula that will calculate the 2nd Friday for the month of any given date (I used TODAY() as the given date)...
[tt]
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+20-MOD(DATE(YEAR(TODAY()),MONTH(TODAY()),1),7)
[/tt]
explanation
[tt]
Date of the FIRST of the month: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
6 is the MOD of any Friday date divided by a week of days
20 is 6 + (2 weeks)
MOD of the FIRST of the month:=MOD(DATE(YEAR(TODAY()),MONTH(TODAY()),1),7)
[/tt]
Of course, the 3rd Friday is...

naaa, but you know that!



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Jontmke]

Thanks, people.
Let me try these and let you know.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

Thanx, pbrodsky. I missed that! have to adjust my formula.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[SkipVought]

Just change the constant -- 13 instead of 20
[tt]
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+13-MOD(DATE(YEAR(TODAY()),MONTH(TODAY()),1),7)
[/tt]
SORRY!


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Jontmke]

OK, I get the formula.
I need a little more help. I have broken the data into sub fields.
Like so:

Monthly	On 2nd Fri   18:30

What I need is an IF statement, but the second field there "On 2nd Fri" is the result of a formula which extracts the data out of the original field. So, what I need is something like this:
IF "the value in field 2" = "On 2nd Fri" then "do the date calculation"
I then can modify the formulas for 1st Sun or 3rd Thu, and so on.
The reason I am bothering with this is I have over 600 entries. Too many to enter by hand.

Jon

There is much pleasure to be gained from useless knowledge. (Bertrand Russell)

 

[SkipVought]

I'd build another table
[tt]
DOM Val
1 sat 0
2 sat 7
3 sat 14
4 sat 21
5 sat 28
1 sun 1
2 sun 8
3 sun 15
4 sun 22
5 sun 29
etc
[/tt]
then I'd lookup the value in column 1 and return the value in column 2 as the value in the date formula
[tt]
=DATE(YEAR(H1),MONTH(A1),1)+VLOOKUP(A2,DOM,2,FALSE)-MOD(DATE(YEAR(A1),MONTH(A1),1),7)
[/tt]
where

A1 contains a date within the month in question
A2 contains the day of month (2nd Fri etc) that also appears in the BOM column of the lookup table.



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[PeterMoran]

Hi,

For what its worth this is a slightly different approach to the problem.

Have a pop-up calendar display on the spreadsheet so you can select the date you need and that be linked to your data for your schedule details. It's much easier to select the second or third Friday off a calendar than code for it!

Here are some urls which will help:

http://www.fontstuff.com/vba/vbatut07.htm

http://www.microsoft.com/exceldev/articles/CalExcel.htm

http://www.ozgrid.com/VBA/excel-calendar-dates.htm

Good Luck!

Peter Moran

 

[SkipVought]

Damn! Why didn't I think of something that simple???

_{Oh, I forgot. I did, and then decided that wasn't what the user was asking for.}

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}