Date and Day Functions

Date and Day Functions

I found an interesting site on Excel for SEOs.
Actually, most of the information on the site pertains to anyone using Excel.
One of their pages was on dates and days. I find a lot of people struggle with date functions
.
=NOW() and =TODAY() are the most common  Date functions.
 They are called volatile functions because they change as the date changes. =NOW() is the more versatile of the two as it tracks both date and time, however, you can format it so that it displays just the date, just the time or both. Whereas, =TODAY only displays and tracks the current date.
They can be very useful. For example, if you create and print a lot of versions of a document, such as a budget. include =NOW(), at the top or in the document header. The =NOW() will display the time - you can even  format down to the second- so you are confident that you have the latest version of your document as you go off to your presentation.

This site has some other date functions that you may be interested in  - particularly NETWORKDAYS and YEARFRAC.
I hope you find them useful.
http://www.acuitytraining.co.uk/white-paper/the-seos-guide-to-excel

Converting Text Dates to a Numeric Dates

Converting Text Dates to Numeric Dates

Sometimes when you import data, particularly dates,  Excel treats the data as text. You can use the DATEVALUE() function to convert text dates to real numeric dates.
 DATEVALUE() returns the serial number of the selected date. Once you have the serial date, you can format it to display as a date.

In this example, the data in Column A are dates that Excel treats as text. (A good way to tell if Excel is treating something as a number or text is to check the alignment. Text is left aligned whereas numbers are left aligned.)  In column B2, I used the formula =DATEVALUE(A2) to change the text in cell A2 into a serial number which displays as 39894.

39894 is the serial number of the date in cell A2.



To convert it to something meaningful, you just need to go the Number group  on the Home Tab

and click on the drop-down arrow beside General



In this particular example, I then selected the short date

The result is shown below:

DATEVALUE() only works on text dates. If you try it on a column of text and numeric dates, you will get a #VALUE error on the entries that are numeric.

Converting a Date to a Calendar Quarter

This is a guest post from Joe Helstrom, CPA.
I thought you would be interested in this as Excel is not that user-friendly when it comes to dates.


How to Convert a Date to a Calendar Quarter


This tip involves some math. We're going to use just the numeric month portion of the date and divide by three. So, if the month is 3 (March), 3 divided by 3 = 1 (First quarter). If the month is 6 (June), 6 divided by 3 = 2 (Second quarter). If the month is 9 (September), 9 divided by 3 = 3 (Third quarter).

What happens when the math is a little messier? If the month is 2 (February), 2 divided by 3 = .66666. We know that this should be in the 1st quarter. If the month is May, 5 divided by 3 = 1.6666 and we know that this should be in the second quarter. The Roundup function needs to be used.

The Excel Roundup function "rounds up" a number. The syntax for Roundup is =Roundup(cell reference, Number of digits). If the number of digits argument is zero, it rounds up to the nearest integer.

So, if we have a date of 2/12/2011 in cell A3, we first need to know the month. The Month function will provide that. In cell B3, type =Month(A3). The result is 2.

We'll combine the Month function into the Roundup function. Using the same cell, B3, type =Roundup(Month(A3)/3,0).

This function takes the numeric month, divides it by 3 and rounds it up to the nearest integer. In this case 2 divided by 3 = .66666 which is rounded up to 1 (First quarter).

Try it on other dates. If, in cell A4, the date is 5/17/2011, in cell B4 type, =Roundup(Month(A4/3,0). Your answer is 2 (Second quarter).

If you have a series of dates that you want to convert to quarters, just copy the formula. You have a quick, easy way to convert dates to calendar quarters.
For courses that address these and other Excel tips and provide CPE to CPAs, visit http://www.cpaselfstudy.com/ or excel-diva.com for non-CPE Excel courses.

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 http://cpaselfstudy.com/category_14/Excel.htm 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 http://www.cpaselfstudy.com/.

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
http://cpaselfstudy.com/item_188/Fraud-Audit-Techniques-Using-Excel.htm


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)

Extracting Different Parts of a Date

Extracting different parts of a date

This is just a quick tip that you might find useful. A lot of times, you have a column of dates and if you could easily extract the month and/or the year you would be able to sort or filter your data by it. It's really quite easy - you just use the Text function and the different date formats. I have a couple of examples below: