Best Microsoft Excel Bloggers

Thursday, June 16, 2011

Using Weekday() function in Fraud Audits

I just looked at the last date I blogged and realized that I am woefully behind!
I have been very busy.  I just finished writing a step-by-step EBook on Pivot Tables.
This time I wrote the EBook both for CPE credit and then created a separate EBook for people looking to purchase the EBook but who don't need CPE credit for it. Go to if you are looking to purchase the CPE course (It is a 5 Hour course). If you just want to purchase the Ebook, you can find the information on that over on the top right-side of the blog under EBooks and Videos for Purchase.

In addition to that, I just finished reviewing an Excel course entitled "Excel Time Value of Money Functions for CPAs " which was written by Joe Helstrom and which can also be found at

Finally, I just finished my first 1 hour video course on Excel Tools and Tips. I am very excited about that and hope to add a few more soon.

Plus, of course it is summer and you know how that goes!

So, I am taking the lazy approach here and picking an excerpt from a course Joe and I wrote recently entitled " Fraud Audit Techniques Using Excel" that I thought you might find useful.  If you are interested in this course go to

Using the Weekday() Function in a Fraud Audit

You can use date functions to identify potential fraud such as checking for activity on weekends.
One quick test is to see if journal entries are being processed on a weekend.
The Weekday function returns the numeric value of the day. So, for example, if you test September 1, 2010, =Weekday(A1) will return a value of 4 since 9/1/2010 was Wednesday the 4th . You could then use conditional formatting to review all the values of 6 and 7 to see if there is anything unusual about the date or you could just sort it.
• Select cell E2 and type =Weekday(A2,1)

• Press Enter

Copy it down the column

Now that we have the weekday values, we could sort by number or we could use conditional formatting to identify Saturdays and Sundays (6 and 7)

• Select your data E2.E23

• Click on the Conditional Formatting icon on the Home Tab
• Select Highlight Cell Rules

• Select Greater Than….

• Enter 5 in the dialog box and click OK

Your data should look like the screenshot below:

You can easily see what transactions were occurring on Saturday and Sunday (6=Saturday and 7=Sunday)

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek