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).
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.
Great function!
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.
When is Friday ?
Today !
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.
[…] When is Friday? If you work involves lots of payroll processing, then having a simple VBA UDF to find out when is the next Friday and when is the last Friday. Thankfully Daily dose of Excel has provided the functions for us. […]
Great function to present to my workmates.
Regards,
Luiz Martins
http://www.g1brazil.com
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? ;)
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.