We help IT Professionals succeed at work.

Excel date formula to a php format

Steven Debock
on
904 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.
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Fixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014

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

Author

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!
Most Valuable Expert 2011
Author of the Year 2014

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.