When Is Friday

I’m working on some VBA to take some of the drudgery out of payroll.

Public Function ThisFriday() As Date

ThisFriday = Date + 8 - Weekday(Date, vbFriday)

End Function

Public Function LastFriday() As Date

LastFriday = Date + 1 - Weekday(Date, vbFriday)

End Function

I thought I needed to know this Friday (pay check day), but I actually need last Friday (end of pay period).

9 thoughts on “When Is Friday

  1. Dick,

    You can do this with built-in Excel functions:
    =INT((NOW()+1)/7)*7 – 1
    This gives you last Friday
    or
    =INT(NOW()/7)*7 + 6
    This gives you next Friday.

  2. Hi, I found your blog on this new directory of WordPress Blogs at blackhatbootcamp.com/listofwordpressblogs. I dont know how your blog came up, must have been a typo, i duno. Anyways, I just clicked it and here I am. Your blog looks good. Have a nice day. James.

  3. I’ve always been wary of working with dates and times. There are too many different ways of referring to the same information or the same way to refer to different information! I’ve found geographic, social, and cultural dependencies. It could even be a question of language as in “Next Friday” and “This Friday.” Most people would definitely ask what “this Friday” means on a Friday! Not to mention that “This Friday” could be “This coming Friday” (as in “I’ll visit you this Friday”) but could be “This past Friday” (as in “I was there just this Friday”).

    Or “Quarter of the hour.” Is it 15 minutes to the hour? Or 15 minutes past the hour?

    As is sometimes the case, I think of how I would address the issue at hand. After some thought and experimenting I came up with 3 formulas. I also compared their results with Dick’s functions and David’s formulas.

    The three formulas I came up with were for “Last Friday,” “This Friday,” and “Next Friday.” For “this Friday” I used the interpretation “This coming Friday or if today is a Friday then today.”

    So, on Friday itself (9/19/2008), David’s formulas yield the same Friday (9/19/2008).

    Dick’s LastFriday function yields today (9/19/2008) and ThisFriday yields the next Friday (9/26/2008).

    The three formulas I came up with are
    LastFriday =B16-MOD(WEEKDAY(B16),7)-1
    ThisFriday =B16-MOD(WEEKDAY(B16),7)+6 and
    NextFriday =B16-MOD(WEEKDAY(B16)+1,7)+7

    On Friday itself (9/19/2008), the three formulas yield last Friday (9/12/2008), today (9/19/2008), and next Friday (9/26/2008) respectively.

  4. And if like a lot of working people, payday comes once a month or twice a month: what does Friday have to do with anything? ;)

  5. I think it’s the whole not-going-to-work-the-next-two-days thing that gets people excited…

    Personally, the macro I need is the “when is my vacation” macro.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.