|
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 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 |
|