Calculate Date Range for Workbook Fiscal Year

I recently created a workbook for a customer that uses Year To Date data.  The report was made simpler because the customer did not want to use the current month so there was no need to calculate the percentage of the current months work days and then apply that percentage to the current month last year.  As with most workbooks the desire here was to automate as much of the data retrieval as possible and this included passing the MS Query the date range for which the query is to be run when the workbook is opened.  

The Fiscal Year runs from February through January.  From March through December the calculation is easily calculated using the DATE and TODAY functions like this for the beginning date =DATE(YEAR(TODAY()),2,1).  This returns February 1st for the current year.  The calculation for the ending date would be =DATE(YEAR(TODAY()), MONTH(TODAY())-1,1).  This formula returns the first day of last month which is what we want since for our purposes the Year To Date excludes the current month.

These simple formulas work great until the beginning of the new year.  In January 2012 the above formulas would produce the following date range February 1, 2012 to December 1, 2011 which is obviously not a valid date range.  February also produces an the invalid date range of February 1, 2012 to January 1, 2012.

Looking at these date ranges also brings up the question of what date range should be used when the workbook is run in February of any given year?  In February the customer wants to see February of the previous year through January of the current year.  In March the date range would be use February only (i.e. February through February).

Additionally the workbook compares data for Year To Date vs. Year To Date Last Year so its necessary to come up with formulas to calculate the beginning and ending date for the previous year.

So, now all you have to do is come up with formulas for the date ranges for the Current Year and the Previous Year.

In the next post I'll show you the formulas that I used in the workbook.

As always, if you have any questions or comments you can leave them here as a comment or email me at randomexcel@att.net.

Cost Overrides on Purchase Orders (cont)

In the workbook for the Cost Overrides the customer needs an easy way to compare the cost of an Item that has had the cost overridden with the Replacement Cost from the current price sheet.  To accomplish this add a new MS Query over the Price Sheet Detail file (PRPMPSD).  Additionally we want to provide the Vendor Name from the Vendor Master file (APPMVEN) and fields from the Vendor Price Sheet Master file (PRPMPSM).

  

We'll return the following fields to our worksheet: Vendor Number, Price Sheet Number, Manufacturer Number, Replacement Cost, Price Sheet Master Type and the Price Sheet Control Number.

The criteria for the query will be PRCD21 = 'C' (we only want to return Price Sheets that are Current) and IVNO07 = [itemnum] (using the brackets allows a value to be passed from the workbook back to MS Query).

The P/S data returned by the query looks this on my worksheet after formatting.

Passing the item number to the query is done from the Main worksheet by selecting a cell in the Item Number column and clicking the "Display Selected Item" button. 

Clicking the button runs code that copies the active cell, selects the Price Sheet Detail worksheet and pastes the Item Number into Cell R5 (row 5 is hidden in the previous screen print).

Now in the Parameters dialog box select the "Get the value from the following cell:" radio button.  Enter the cell reference in the box (the best way to do this is click in the box and then click cell C5).  Click the "Refresh automatically when cell value changes" check box.  Click OK.

Now whenever a new item is selected from the main worksheet and the button is clicked  the query will run and display data for the selected Item Number.

Here's the code used by the "Display Selected Item" button.

This code is used by the "Exit" button to return the user to the Main worksheet.

 

In the next post we'll cover additional enhancements that were requested for this workbook

As always, if you have any questions and/or comments either leave it here as a comment or email me at randomexcel@att.net.

New Email Address

If you would like to email me you can use the following email address:

randomexcel@att.net

Cost Overrides on Purchase Orders

This week I had a request from a customer for a workbook to display all Purchase Order line items that have had the cost overridden.  This post will detail the construction of that workbook.

When beginning a project you need to determine as closely as you can exactly what the person requesting the workbook want but frequently you'll find that even though they "think" they know what they want you will end up making additions or changes to the workbook before you complete the project.  Just be aware that the better your understanding of what the end user wants/needs the easier the project will be.

When starting on a new workbook I usually begin by entering the headings based on what the user has requested.  You need to consider the fields that will be used to populate the data for each heading and make additional fields that will be used but not displayed in your workbook (i.e. dates will display in one cell in Excel but require three or four fields from the HD database).  This workbook has no such requirements but it should be kept in mind.  The image below shows the worksheet with the headings entered.

Now we select cell B6 and create the MS query to return the data to the worksheet.  Based on the headings we will be extracting data from the Purchase Order Line Item File (POPTRL) and the Item Master File (IVPMSTR).  These two files are joined on the Item Number field (IVNO07).  The worksheet does not display a date but a date is used as part of the record selection process.  The date that will be used is the Last Update date.  For the date selection criteria the HD fields are joined to create a single field that can be passed as a parameter to the query.  Since date fields in HD are numeric this is accomplished by summing the fields like this: POCC01*1000000+POYR01*10000+POMO01*100+PODY01.  This result of this formula for July 1, 2011 is 20110701 which can be passed to the query to return records for the requested date.  The value for the date is set in brackets so it can be changed from the worksheet (i.e. [chgdate]).  Since the user is only interested in records where the cost has been overridden then the criteria also includes the Line Item Cost Override Code (POCD17).  After including the criteria and selecting the fields to be returned to the worksheet the query will look like this:

When the query is run to return the data to the workbook a dialog box will display requesting the parameter (date) to be passed to the query.  Enter the desired date and click OK.

The data is now returned to the worksheet as below:

NOTE: Since this is data from a Customers Production the Product Numbers have been blanked out.

Now add a heading for the date select in cell D4 (i.e. Enter Date >>) and enter a date in E4.  In cell M6 enter this formula - =SUM(YEAR(E4)*10000+MONTH(E4)*100+DAY(E4)).  This formula provides a field that will be used as the parameter for the query.  Now open the Parameter dialog box.  To open the dialog box in Excel 2007 click Data Tab > Connections  and select the query and click Properties > Definition Tab > Parameters button; for 2003 select a cell in the data returned by the query and right click and click Parameters.  In the Parameters dialog box

click the radio button "Get the value from the following cell:" and the check box "Refresh automatically when cell value changes".  Click in the field below "Get the value..." and then click in cell M6.  Click OK.  Now when the user changes the date in cell E4 the query will run and return data for the entered date.  Test the workbook to verify that it functions correctly and returns the correct data.

Lastly format the workbook as desired and test once more and it should be ready for your user.

Here's my formatted worksheet.

As always if you have any comments and/or questions leave a comment here or email me at the office.