John's Blog: Finding the last day of week before a given date

Everyone and his dog has a 'blog these days, right? So why not me? If you are interested in anything I'm saying, great! Let's talk about it! (Leave comments.) If not, try one of my other links. Better yet, why are you wasting your time staring at your computer screen, go do something real!

Let me know if you'd like to be to be updated whenever I post a new article. It won't happen every day!

Finding the last day of week before a given date

Wednesday, June 06, 2007

Occasionally I need to derive the formula to find the last (whatever day of week) before a given day, such as the previous Sunday or the previous Monday. I do this so infrequently that I always forget where I have already done it or what the formula is, so I usually derive from scratch every time. I could Google for it, but I figure that would take about as long as deriving it would. But, now that I've done it yet again, maybe if I put it in my blog, I'll be able to find it easily the next time. Or maybe it will even help others who might find it by Googling, maybe you! (Click title to read more...)

I'm trying to keep this language-independent, but there need to be some givens:

You have a function in your language to return a day of the week as a number. In this case, I'm assuming 0 = Sunday to 6 = Saturday, which is the standard for C and its derivative languages like Perl and PHP. Alternately, the MS Excel function WEEKDAY returns 1 = Sunday to 7 = Saturday by default, so you would implement this formula in Excel by simply subtracting 1 where the variable w is used. (I used Excel to test this.)
You need a modulo function. In C, Perl, PHP, etc., this is the binary operator %, so that's what I use here. MS Excel and some others use the function MOD( n, m ) instead.

Given Variables:

w = Day of Week (DOW) number of the date you are starting from, such as today. This will be 0 - 6 as described above.
e = DOW number of the date you are trying to go back to, such as 0 to find the previous Sunday, 1 to find the previous Monday, etc.

We want to find the number of days between our starting day and the day we are going back to:

Difference in days (d) = w - e. The sticky point is that we need to provide for "wrapping around" to the prior week. To do this, we add 7 and then modulo the result by 7:

d = ( w - e + 7 ) % 7

Now we just subtract this number of days from the current day. How this is done depends on your language.

In languages that represent date/times as a number, with the whole part being the day and the decimal part being the time, you can just subtract as a number. For example, in my MS Excel testing spreadsheet, if the starting date (maybe today) is in cell A1, and I'm going back to the previous Monday (DOW 1), the formula is:

=A1 - MOD( WEEKDAY( A1) - 1 - 1 + 7, 7 )

Note that we subtracted 1 from the WEEKDAY function to make it 0-6 instead of 1-7, and used Excel's MOD function.

In a language such as C, where timestamps are represented as number of seconds since a fixed time (like Jan 1 1970, the Unix epoch start), just take d and multiple it by seconds in a day, 86400, and subtract that from the starting day to find the finish day.

Or, your language may have a date difference function where you can specify the number of days.

Hope you found this helpful!

Labels: computer

2 comments

2 Comments:

Great Post, thanks

By Anonymous, at 6:07 PM

I've been trying to find a solution for this for ages! Fantastic post! Thanks.

By Anonymous, at 1:22 AM