Tag Archives: date functions

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.

 

 

 

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry

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

    - Alex

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott