Dec 11, 2012

Formula Example - What Day of the Week is/was it?

I found a post over at the Apex Code board at DeveloperForce.com from someone who needed assistance creating a formula to determine how many Fridays occurred between two given dates.

Easy enough, I thought.  I'll just use a date method w/in a loop and count how many times Friday occurs.  I didn't find any date methods or dateTime methods that return the day of the week.  It seems like such a basic need; perhaps I glazed over it.

A quick Googlin' led me to one example, nearly 100 lines long.  It shouldn't be that difficult; I'd rather spend 100 lines showing you how to do the math and then 1 line of code using that math.

A little more poking around and this is what I found:

a = (x - y) (mod 7)

a = Day of the week index
x = a date in which you know the day of the week
y = the date in which you want to know which day of the week it was or will be

In a nutshell, figure out the number of days between your two dates (x and y), divide that number by 7, and the remainder (mod 7) will tell you the day of the week, based off of the known day of x.

We still need "x" - the copious amounts of calendar generators on the web all agree that 1/1/1900 was a Monday.  

Let's see it in practice, using 12/11/2012 as an example or proof (just pretend you don't know it's Tuesday).  12/11/2012 will serve as our "y" variable.

a = (12/11/2012 - 1/1/1900) (mod 7)
a = 41,252 (mod 7)
a = 41,252 divided by 7 leaves a remainder of 1
a = 1

Since we know 1/1/1900 was a Monday, we know that 1/2/1900 was a Tuesday, 1/3/1900 a Wednesday and so on all the way up to 1/8 when everyone has a case of the Monday's again.  There are 7 days between 1/8 and 1/1 and dividing 7 by 7 leaves you with a remainder of 0.  0 will represent Monday.  1/9/12 is a Tuesday and there are 8 days between 1/9 and 1/1.  Dividing 8 by 7 leaves you with a remainder of 1.  1 represents Tuesday.  The same logic tells you 2 is Wednesday, 3 is Thursday, 4 is Friday, 5 is Saturday, and 6 is Sunday.  In the example above, a = 1, therefore 12/11/2012 must fall on a Tuesday.

So all we need to do now is plug in our dates and use a few methods that do exist.

We'll use:
math.mod()
date.daysBetween()



Go ahead, try executing that code and seeing what the debug output tells you.

So now we have an easy mechanism for determining the day of the week for a given date. Let's go the extra mile and see how we can easily create a bit of code now that determines how many how many of a certain day occurred between two dates.