Comparing dates php/phpExcel

Question

I get a date value from an excel file and I change it to date like this

$dateEx = $sheet->getCellByColumnAndRow(2,$line)->getValue();      
$date = date('Ymd',($dateEx - 25569)*24*60*60);

And I have 2 another date time that I convert from String to date

  $dateOuverture = '20150306';
  $dateFerm = '20150906';              

  $dateOuverture = new Datetime($dateOuverture);
  $dateOuverture = $dateOuverture->format('Ymd'); 


  $dateFerm = new Datetime($dateFerm);
  $dateFerm = $dateFerm->format('Ymd');

and when I want to compare the date with an if it doesnt works

if($date<=$dateFerm && $date>=$dateOuverture){
     echo "Im in the if";

}

what did I wrong?

Thanks for help

Convert both strings to time. Using `strtotime`. Dates in versions before 4.2 can't be compared http://php.net/manual/en/function.strtotime.php — Jacques Koekemoer, Sep 09 '15 at 08:22
@JacquesKoekemoer, formats are valid recording to ISO8601 Notations (http://php.net/manual/en/datetime.formats.date.php) — sanderbee, Sep 09 '15 at 08:24
from excel I get numbers like this 42164.536761227 instead of dates — I_G, Sep 09 '15 at 08:24
And what does the second argument of date contains after your calculation? — sanderbee, Sep 09 '15 at 08:25
why not just convert them all up to datetime objects then make your comparisons — Kevin, Sep 09 '15 at 08:25
the value from Excel I get is a date value like my strings what I get after this $date = date('Ymd',($dateEx - 25569)*24*60*60); is this 20150331 — I_G, Sep 09 '15 at 08:27
@Ghost: I already tried to convert them all to datetime but it didnt work — I_G, Sep 09 '15 at 08:31
@sarikaya yes you used datetime, but you're comparing the `->format`ted string, directly convert the constructed object, use the object, it'll work, convert `$date` to datetime also, then make your comparisons — Kevin, Sep 09 '15 at 08:34

Mark Baker · Answer 1 · 2015-12-22T19:50:53.367

2

from excel I get numbers like this 42164.536761227 instead of dates

Those numbers are MS Excel serialized timestamp values

This is why PHPExcel provides functions to convert between MS Excel serialized timestamp values and PHP DateTime objects or Unix timestamps (and vice versa)

$dateTimeObject = PHPExcel_Shared_Date::ExcelToPHP(42164.536761227);
echo $dateTimeObject->format('Y-m-d H:i:s');

or

$unixTimestamp = PHPExcel_Shared_Date::ExcelToPHP(42164.536761227);
echo date('Y-m-d H:i:s', $unixTimestamp);

You can then use standard PHP functions to do any comparisons

edited Dec 22 '15 at 19:50

answered Sep 09 '15 at 10:18

Mark Baker

209,507
32
346
385

In the second example, it's actually PHPExcel_Shared_Date::ExcelToPHP(42164.536761227); – Isidro Moran Dec 22 '15 at 19:49

sanderbee · Answer 2 · 2015-09-09T09:29:25.497

1

PHP date functions accepts a timestamp as second argument, no ISO8601 date format. Strtotime does accept that, and returns a valid timestamp. Combine the two like this:

$dateEx = $sheet->getCellByColumnAndRow(2,$line)->getValue();
$iso8601Date = ($dateEx - 25569)*24*60*60;    
$date = new DateTime($iso8601Date);

See this link for more about date formats

Compare the DateTime objects

$dateOuverture = '20150306';
$dateFerm = '20150906';

$dateOuverture = new Datetime($dateOuverture);
$dateFerm = new Datetime($dateFerm);

if($date<=$dateFerm && $date>=$dateOuverture){
    echo "Im in the if";
}

edited Sep 09 '15 at 09:29

answered Sep 09 '15 at 08:31

sanderbee

694
7
24

Try comparing two datetime objects. See my edit and this link: http://stackoverflow.com/questions/961074/how-do-i-compare-two-datetime-objects-in-php-5-2-8 – sanderbee Sep 09 '15 at 08:43

Comparing dates php/phpExcel

2 Answers2