Steven Debock

asked on

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.

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

Last Comment

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

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

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.

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.

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) . '%';
```

ASKER

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!

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

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

Thank you all!

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

PHP

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

125K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY