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
Solved

Excel date formula to a php format

Posted on 2014-11-15
7
222 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
  • 4
  • 2
7 Comments
 
LVL 109

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 109

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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 109

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 109

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
push Book Today button to right 10 37
Magento: different theme for Pc and for mobile devices 4 34
IP 10.0.1.2 / 255.0.0.0 61 56
Web Easy Professional 10 Embedded Facebook 1 18
This article discusses how to create an extensible mechanism for linked drop downs.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

829 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