There is a known issue in Phoenix 1.1 (Phoenix WNL 6.1, PHoenix Connect 1.1, and Phoenix NLME 1.0) that Excel files with dates or times formatted cells might not import correctly in Phoenix (Track ID QC 9108). If you experience this problem, the best workaournd is to save the Excel file as a .csv file and import the .csv file instead The problem was that excel stores all the time values as full time not just a string so Phoenix used to import the value from the cell. This will be corrected in the next version (Phoenix WNL 6.2, Phoenix Connect 1.2, and Phoenix NLME 1.1) as Phoenix will check if there are date/times and will grab the formatted text instead of the value.
Dear Ana! Now it hit me. I received a CSV file with dates and times in separate columns. Fine. If I import the file in Excel dates and times are recognized, but importing the same file in PHX6.2 both columns are imported as text. Example: 15APR2011 and 08:00 (text as given in the CSV-file, no character delimiters) are converted to date/time 2011-04-15 and 07:55:00 in Excel (displayed as 15-Apr-11 and 07:55; I can format them any way I want, add dates/times…) – but in PHX I get plain text only. If I save the CSV in Excel‘s format, again the import ends with plain text. I don’t get the point that the issue was corrected in v6.2. Now I have to start setting up a series of custom transformations (essentially extracting substrings [BTW: search() still wrong; see ID 00133371], converting into numbers, date(), hour(), min()…). Boring and error-prone. I thought it would be easier to add times to dates in Excel in order to get a timestamp. If I import the excel file the column is imported as numeric. 2011-04-15 07:55:00 is imported with 40648.3333333333 (from Excel: 40648.3[color=#FF0000]298611111[/color]). Interesting, because in PHX we get year() -2147483648, month() 4, day() 17, hour() 8, minute() 0. Resonable, because the timestamp is wrong after the first decimal. No idea why the year is negative – year(40648) in PHX gives correctly 2011. Ah – I see, if I use year(trunc()) and month(trunc()), I get 2011 and 4 – but end of story with day(trunc()) 17 (why?) If I save the Excel-file as CSV, I get the ISO-date (YYYY-MM-DD HH:MM), which is imported in PHX as text. OK, at least the transformations are a little bit easier now – or do I miss something completely?
Hi Helmut, I think the confusion might be about what actually was fixed. The defect corrected in PHX 1.2 was Track Numer 9108. The issue corrected was that if you had a date in Excel (without a time), PHX upon importing was adding a 12:00AM to the date. In order for this to be impored correctly the workaround was to create a .csv file so the date will be imported without a time. DateTime, date and time cells are all imported as text. After PHX imports the datetime stamps as text you can still do calculations by converting this text to timevalues. For example to find the difference in a datetime in hours you can write in a custom column transformation: hour(datevalue(x)-datevalue(y)) Example in the attached phx project (phx 1.2). Hope this helps, Ana [file name=DateExample.phxproj size=60641]Certara | Drug Development Solutions
DateExample.phxproj (59.2 KB)
Hi Ana! Hey, that helped a lot. Life could be so much easier if you know where the right buttons to press are. THX!