Solved

Excel date formula to a php format

Posted on 2014-11-15
7
249 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

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 …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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.

707 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