Solved

Excel date formula to a php format

Posted on 2014-11-15
7
229 Views
Last Modified: 2014-11-16
Hello Experts

I have a calcucation in excel that i want to "convert" to php.
This is the excel data example
cell B14 = 2,73%
cell B11 = 04/03/2015
cell B10 = 19/11/2014
cell F13 = 100%*B14*(365-B11+B10)/365
cell F13 outputs 3,56%

I have all the cell data (B11, B10, B14) in a mysql db.
But how to go about the F13 = 100%*B14*(365-B11+B10)/365 in php? If this is at all possible of cours.
0
Comment
Question by:Th0R
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 40445067
A few questions to clarify... What is the output you expect to get from your calculation?  What are the input values?  What are the rules to transform the inputs into the output.  These calculations are usually done with date() and strtotime() functions in PHP.  

This article explains how PHP handles DATETIME values.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 300 total points
ID: 40445071
That works in Excel because Excel's real date format is an integer that gets formatted for display.  See here http://office.microsoft.com/en-us/excel-help/date-and-time-functions-reference-HP010342402.aspx to see if you can use these functions to find out what Excel is actually doing.  Then you can write a procedure in PHP that does the same thing.  Note that Excel data ranges start at the year 1900 while PHP/Unix time stamps start at 1970.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40445149
I think it's level-dependent about when PHP/Unix timestamps start.  This is misleading.  See the ChangeLog here.  Consider the possible effect of 64-bit Unix Timestamps as shown in the Notes here.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40445159
That's very good info... but it did not contradict anything I said.  All of those pages including the 64-bit reference say that PHP and Unix timestamps are referenced to 1970-01-01.  Excel and the formula in the question are referenced to 1/1/1900 (all Microsoft products that I know of use this 'standard').  This means that the asker must figure out how to convert the formula with the Excel version of date and time to the PHP version that has different reference point.

From http://support.microsoft.com/kb/214094
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40445655
Please see http://iconoun.com/demo/temp_th0r.php

This clearly does not produce the output you want, so we may need to get closer to each of the steps of the arithmetic to see what is wrong.  Please see the comments in the code.
<?php // demo/temp_th0r.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28562965.html#a40445159
// REF http://php.net/manual/en/function.gregoriantojd.php

/**
 * FROM THE QUESTION AT E-E
 * cell B14 = 2,73%
 * cell B11 = 04/03/2015
 * cell B10 = 19/11/2014
 * cell F13 = 100%*B14*(365-B11+B10)/365
 * cell F13 outputs 3,56%
 *
 * I have all the cell data (B11, B10, B14) in a mysql db.
 * But how to go about the F13 = 100%*B14*(365-B11+B10)/365 in php? If this is at all possible of cours.
 */

// ELEMENT THAT APPEARS TO BE A PERCENTAGE, SHOWN AS A DECIMAL FRACTION
$b14 = '0.0273';

// ELEMENTS THAT ARE IN THE MySQL DATABASE, SHOWN IN STANDARD ISO-8601 FORMATS
$cellB11 = '2015-03-04';
$cellB10 = '2014-11-19';

// COMPUTATIONS SHOWN IN EXCEL F13 CONVERT TO DAY INTEGERS - BUT IS THIS RIGHT?
$b11 = strtotime($cellB11);
$b11 = gregorianToJD( date('m', $b11), date('d', $b11), date('Y', $b11) );

$b10 = strtotime($cellB10);
$b10 = gregorianToJD( date('m', $b10), date('d', $b10), date('Y', $b10) );

// DIFFERENCE IN DAYS APPEARS TO BE 260
$x = (365 - $b11) + $b10;

// DIFFERENCE IN PERCENTAGE APPEARS TO BE 71%
$y = $x / 365.0;

// MULTIPLIED BY CONSTANT IN B14 DOES NOT PRODUCE 3.56%
$z = $y * $b14 * 100.0;
echo PHP_EOL . number_format($z,2) . '%';

Open in new window

0
 

Author Closing Comment

by:Th0R
ID: 40445727
Thank you both for your input.
both comments about datetime functions and the excel Serial numbering for dates put me on the right track

Implemented this reverse function:
http://forums.phpfreaks.com/topic/157341-convert-serial-date-into-ddmmyyyy/

i managed to reproduce the excel formula in php
round($int * (365 - date2serial($vcdate) + date2serial($vad->format('Y-m-d'))) / 365, 2);

It seems to do the job with the same results as in excel.

Thank you all!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40445756
Thanks for the points, but be careful about the assumption that 86,400 seconds == 1 day.  This will be wrong twice a year in locales that observe daylight savings time.

<?php // demo/temp_th0r.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28562965.html

date_default_timezone_set('America/Chicago');
$d1 = 'Sunday, November 2, 2014';
$d2 = 'Monday, November 3, 2014';
$t1 = strtotime($d1);
$t2 = strtotime($d2);
$sx = $t2 - $t1;
var_dump($sx); // NOT 86,400 -- not 24 hours * 60 minutes * 60 seconds

Open in new window

0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP function parameters defined 14 42
Increase counter and attr inside a while loop 15 37
Put POST values into cookies. 14 34
SP converting date time to date and time separately 2 19
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question