Category Archives: Date functions

DATEVALUE Function

Excel has a number of date functions.  Unfortunately, they don’t all work if Excel treats the date as text.  When working with downloaded data, sometimes the date must be converted from text to a format recognized by Excel.

One method is to use the DATEVALUE function.  This function converts a date in text format to a number that represents the date in Excel date-time code.
Open the exercise workbook and select the Datevalue worksheet.

In cell A1, is the text ‘12/31/11.  In cell B1, type =DATEVALUE(A1)
datevalue 1

The value in cell B1 is the Excel date-time code.  However, it requires some formatting to make sense.
Format cell B1 as a date either by using the Home menu and Number selection or the Home menu Format Cells selection.

datevalue2Format B1 in the date format of xx/xx/xxxx.datevalue3

You can tell that the date in cell A1 is in text format due to its left justification in the cell while the date in B1 is a date by its right justification in the cell.

This is an excerpt from our Must Know Functions for CPAs.

EOMONTH

Date Function
EOMONTH

EOMONTH screenshot

screenshot of a function dialog box

EOMONTH displays the serial number of the last day of the specified month.
You can use this to calculate maturity dates and due dates that occur on the last day of the month.
In the example below, I have 1/2/2016 in cell A1. In another cell, I have typed =EOMONTH(A1,2) which tells Excel to look at the date in cell A1 and display the serial date of the last day of two months out which is March 31, 2016.
I keep saying serial number. In the screenshot below it shows 42460 as the answer even though in the bottom of the dialog box you can see the Formula result is 3/31/2016. If you want to see the date format then you need to go to the number format and select Short Date or some other date format.
If I had typed =EOMONTH(A1,1) it would have displayed the serial number of one month out which would be February 29, 2016.  If I had typed =EOMONTH(A1,-1) then it would display 12/31/2015.

Using Workday to Calculate Invoice Dates

This is just a quick tip as Excel is not necessarily that date friendly.
If you want to calculate an invoice date or a project completion date use the WORKDAY() function.
Select a cell and then select the Formula tab and then select Date& Time

The syntax is as follows:
–Start_date Required. A date that represents the start date. Make sure to use a date and not text
–Days Required. The number of nonweekend and nonholiday days before or after start_date.
A positive value for days yields a future date; a negative value yields a past date.
–Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates

In the example below, I selected cell D3 which is where I wanted the due date to display.  F1 represents the number of days the invoice is due after from the invoice date which is in cell B3.  My start date was B3 and the number of days was 30, as shown in F1. I opted not to include holidays which is optional.
The resulting answer is 42095 which is a serial number.  This is not incorrect – it is just not formatted as a date. 
 Depending on how you have formatted your spreadsheet you may need to format D3 to display the actual date rather than the serial number. 

workday()

I opted to set up F1 as an input cell and used absolute cell references so that if I had a series of invoices I could just copy the formula down. The advantage of the input cell is that if I decide to give everyone 45 days instead of 30 days, I only have to change the value in F1.

workdkay2

 

 


If you use dates a lot you may be interested in an entry on my Excel-Diva blog entitled Converting a Date to a Calendar Quarter.

 

 

 

  • Thank you Patricia. I really love your courses. I learned so much about excel this weekend!”

    - Connie K.

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “The course on ethics was one of the best correspondence courses I ever used.  It was well organized and the examples used were extremely helpful.”

    - Tommy R.