Working with HD Date Fields

If you've worked with the date fields in HD previously you're aware that HD does not use actual date fields but uses numeric fields for century, year, month and day.  For accounting periods the day field is excluded.

In this example we'll use the Invoice and Sales Period date fields from the Invoice Header file (OEPTOHY).  The Invoice fields are OECC01 (century), OEYR01 (year), OEMO01 (month) and OEDY01 (day).  The Sales Period fields are OECC08 (century), OEYR08 (year) and OEMO08 (month)

Below data is being pulled from the Invoice Header file in HD.  The Order Number, Invoice date fields and the Sales Period date fields have been selected.  The century and year fields for both Invoice date and Sales Period have been combined to create create a "standard" year field.  This could also be done after the data is returned to Excel.  The formula is century times 100 plus the year (OECC01*100+OEYR01).

Here the data has been returned and the DATE function used to create a date from the HD fields.  For the Inv Date the formula is =DATE(B2,C2,D2).  Default formatting was used for Inv Date.  For the Sales Period the formula is =DATE(E2,F2,1).  The 1 is used for the day argument because Accounting Periods in HD have no Day field.  Formatting for Sales Prd was changed to display only month and year.