Converting Date in HQ Manager Report

Sep 02, 2010 2 Replies
D

I have modified a PO report to include PurchaseOrder.LastUpdated as a column. Unfortunately the report include the time stamp in the LastUpdated date field. When I "Copy as Table" & paste it to my Excel sheet, the time stamp is also pasted. Even though I formatted my Excel column to Date format 1/1/2001, the time stamp still shows if I were to select on any of the date cells. This messes up my cell formulas in excel.



The only way to get around this is to format my the date column in my excel to 1/1/2001, copy the whole column, paste it a notepad, then re- copy it from notepad, then paste it to the date column again in excel. By doing this, it removes the time stamp. But this could be tedious if I were to run 8 reports.



Is there anyway to change the formula below to exclude the time stamp?



Begin Column FieldName = "LastUpdated" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Last Updated" VBDataType = vbDate Formula = "MAX(PurchaseOrder.LastUpdated)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1035 GroupMethod = groupmethodNone ColFormat = "" End Column


Vote
Twitter Facebook LinkedIn
D

I've found them.

For PO's or Inventory Transfers reports: VBDataType = vbString Formula = "MAX(CONVERT(datetime, CONVERT(varchar(10), PurchaseOrder.LastUpdated, 101), 101))"

For Detailed Sales report: VBDataType = vbString Formula = "CONVERT(datetime, CONVERT(varchar(10), [Transaction].Time, 101), 101)"

Vote
Twitter Facebook LinkedIn
C

Try this setting in col format

ColFormat = "mm/dd/yy" or "dd/mm/yy" if European.

" snipped-for-privacy@gmail.com" wrote:

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required