Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Excel date formula to a php format

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
Th0R
Asked:
Th0R
  • 4
  • 2
2 Solutions
 
Ray PaseurCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
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
Independent Software Vendors: 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!

 
Dave BaldwinFixer of ProblemsCommented:
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
 
Ray PaseurCommented:
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
 
Th0RAuthor Commented:
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
 
Ray PaseurCommented:
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

Independent Software Vendors: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now