Novice still: I need a forumula to use in a calendar to highlight every 2nd Wednesday of every month

innkeeper9 · #1 10-22-2019, 07:45 PM

I have a perpetual calendar from

https://www.vertex42.com/calendars/p...-calendar.html

This calendar is free for personal use and can be altered. What I want to do is have a formula, if possible, that will highlight every 2nd Wednesday of every month no matter what the year. I don't know how to create forumulas, and being a perpetual calendar there are a lot of forumlas already in it, so I don't know if it would be possible.

I just thought this would save time and I wouldn't have to make a new calendar each year marking those Wednesdays so I know when my SS check is being deposited.

Thank you for any help.

gmayor · #2 10-22-2019, 10:45 PM

Have you considered using Outlook, where it is a simple matter to enter a recurring appointment every second Wednesday of the month, and you can include reminders?

innkeeper9 · #3 10-23-2019, 12:20 AM

Thanks so much for the response. Probably I should have explained more in depth.

I don't actually want to use the calendar. What I've been doing over the years is get dates of my checks each year, then download a free calendar for the year set up as landscape with 4 months per line. Then I sceen capture that and open the image in my graphics program and put an transluscent colored circle on the date for the checks. The result is a small .jpg image that sits on my desktop and I click and it opens in paint.

When I saw the perpetual calendar and I was able to set it up the same landscape I was hoping it would be possible to have a formula that would highlight that 2nd Wednesday of each month. That would mean I wouldn't have to do anything but screen capture it the beginning of each year and save the image.

If there were a smiley for being lazy or maybe just older now I'd use it.

Thanks again!

p45cal · #4 10-23-2019, 02:39 PM

In the Year sheet (only) of the attached is conditional formatting which highlights in yellow the 2nd Wednesday of each month.

innkeeper9 · #5 10-23-2019, 04:12 PM

Thank you, thank you, thank you. That is perfect. I don't know what you did, but

Ruth

ArviLaanemets · #6 10-23-2019, 10:41 PM

A formula to calculate 2nd Wednesday of month from any date (DOM) of this month:

Code:

=DATE(YEAR(DOM);MONTH(DOM),1) + (2-1)*7 + (WEEKDAY(DATE(YEAR(DOM),MONTH(DOM),2))<3)*(3-WEEKDAY(DATE(YEAR(DOM),MONTH(DOM),2))) + (WEEKDAY(DATE(YEAR(DOM),MONTH(DOM),2))>3)*(7+3-WEEKDAY(DATE(YEAR(DOM),MONTH(DOM),2)))

I have used WEEKDAY() with 2 as 2nd parameter (weekdays are numbered from Monday to Sunday - i.e. European week definition);
In ...WEEKDAY(...,2)<3... and ...WEEKDAY(...,2)>3... the number 3 represents weekday number the returned date must have - for Wednesday it is 3;
In ...(2-1)*7... the number 2 represents the number of occurrence of asked weekday in this month.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open Outlook to Month View Calendar	komobu	Outlook	5	03-17-2015 09:26 AM
Adjust display in calendar month view	Dovidle	Outlook	1	01-07-2015 10:16 AM
Configure for 30 days, not calendar month	teapea21	Outlook	1	07-31-2014 09:05 AM
start-stop month dates in calendar	madison320	Project	0	06-24-2011 08:15 AM
Can't see delegates calendar past next month.	enviroko	Outlook	3	08-08-2010 01:10 PM