ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  Charttime to Excel year, month, day
Posted by Paul Huffman on Feb-14-2019 07:37
I had an idea that I could check my data's  dates and values line form in Excel before I charted them or to compare to my chart https://www.chartdir.com/forum/download_thread.php?bn=chartdir_support&pattern=Paul+Huffman&thread=1542302020  but my quick check is getting complicated.  I modified my charting php script to print out my rows, then I copied and pasted from a browser window to a blank Excel spread sheet, separating the fields with Excel's Text to Columns.  But my dates are in charttime. What would be the formula to get them into Excel year, month, day?

In Access, I have used queries like SELECT Format(([Flow.DATE]+2209161600)/86400,"mm/dd/yyyy") AS Expr1, Flow.QD, Flow.SITE
FROM Flow
WHERE (((Flow.SITE)="YRPW"))
ORDER BY Flow.Date;

In Excel it would be =(A1/86400/1000)+25569

But that is to convert Unix time stamp dates. To convert from Chart Time,  it would be a much bigger number than 2209161600, I think.

  Re: Charttime to Excel year, month, day
Posted by Peter Kwan on Feb-14-2019 09:56
Hi Paul,

In PHP, you can use getChartYMD to convert chartTime into a number representing yyyymmdd, such as 20190214. It would then easy for Excel to separate out the year, month and date parts. See:

https://www.advsofteng.com/doc/cdphp.htm#phpchartdir.getChartYMD.htm

For example, in PHP:

$arrayOfYMD = array_map(getChartYMD, $arrayOfChartTime);

You can then print out the $arrayOfYMD in HTML and copy and paste them to your Excel spreadsheet.

Regards
Peter Kwan

  Re: Charttime to Excel year, month, day
Posted by Paul Huffman on Feb-15-2019 06:14
Works great.  Thank you.  I didn't think of doing this on the PHP side.

  Re: Charttime to Excel year, month, day
Posted by Paul Huffman on Feb-15-2019 06:55
I had a little difficulty using the date string in Excel.  I separated the year, month, and day into different columns with Excel LEFT, MID, and RIGHT functions, then used Excel's DATEVALUE function like =DATEVALUE(E2 & "/"&F2&"/"&G2)  or more simply the DATE function like =DATE(E2,F2,G2).


https://www.dropbox.com/s/6ute32dpo1w5nhh/exceldates.PNG?dl=0

  Re: Charttime to Excel year, month, day
Posted by Paul Huffman on Feb-15-2019 06:59
I could have combined Excel's string chopping functions and the DATE function all in one Excel formula.   https://support.office.com/en-us/article/DATE-function-E36C0C8C-4104-49DA-AB83-82328B832349

  Re: Charttime to Excel year, month, day
Posted by Peter Kwan on Feb-15-2019 20:06
Hi Paul,

I would probably use arithmatic methods to separate the yyyymmdd. For example, the year is the quotient of the number divided by 10000, and the day of month is the remainder of the number divided by 100. The entire date is:

=DATE(A1/10000, MOD(A1/100, 100), MOD(A1, 100))

A1 is the cell containing the number yyyymmdd

Regards
Peter Kwan