php merge dateranges code algorithm

Here is a code for merging date-ranges in array. The method gives new array, with the maximum mergining between the date-ranges, meaning if there is a date-range that already exists with-in other, it can just skip, so just if two or more dates can be combined into one big date-range - it will be made :) . 

 

-------------------------------Example #1:

  array 
  0 => 's' => '2020-01-06' 'e' => '2020-01-10'
  1 => 's' => '2020-01-07' 'e' => '2020-01-12'
  2 => 's' => '2020-01-13' 'e' => '2020-01-16'
  3 => 's' => '2020-01-22' 'e' => '2020-01-24'

RESULT:
array 
  0 => 's' => '2020-01-06' 'e' => '2020-01-16'
  1 => 's' => '2020-01-22' 'e' => '2020-01-24'

 

-------------------------------Example #2:

array (size=8)
  0 => 's' => '2020-01-01' 'e' => '2020-01-01'
  1 => 's' => '2020-01-03' 'e' => '2020-01-03'
  2 => 's' => '2020-01-09' 'e' => '2020-01-10'
  3 => 's' => '2020-01-11' 'e' => '2020-01-22'
  4 => 's' => '2020-01-14' 'e' => '2020-02-02'
  5 => 's' => '2020-02-02' 'e' => '2020-02-03'
  6 => 's' => '2020-02-04' 'e' => '2020-02-04'
  7 => 's' => '2020-02-05' 'e' => '2020-02-07'

RESULT:
array (size=3)           
  0 => 's' => '2020-01-01' 'e' => '2020-01-01'
  1 => 's' => '2020-01-03' 'e' => '2020-01-03'
  2 => 's' => '2020-01-09' 'e' => '2020-02-07'

 

 

And here is the CODE:

$arrDateranges = array(
    array('s' => '2020-01-22', 'e' => '2020-01-24'),
    array('s' => '2020-01-06', 'e' => '2020-01-10'),
    array('s' => '2020-01-07', 'e' => '2020-01-12'),
    array('s' => '2020-01-13', 'e' => '2020-01-16'),
);
$arrMerged = mergeDateRanges($arrDateranges);
var_dump($arrDateranges);
var_dump($arrMerged);

//---------------------------------------------------------

// helper funciton to get NEXT date (or any modified date)
function getRelativeDate($p_sDate, $p_sModify, $p_sFormatIn = 'Y-m-d', $p_sFormatOut = 'Y-m-d') {
    $oDT = DateTime::createFromFormat($p_sFormatIn, $p_sDate);
    $oDT->modify($p_sModify);
    return $oDT->format($p_sFormatOut);
}

function mergeDateRanges($p_arrDateranges) {
    // sort by start date
    usort($p_arrDateranges, function($a1, $a2) {
        return $a1['s'] === $a2['s'] ? 0 : ($a1['s'] < $a2['s'] ? -1 : 1);
    });
    
    $arrMerged = array();
    $arrLastDR = null;
    foreach ($p_arrDateranges as $arrDR) {
        if ($arrLastDR === null) {
            $arrLastDR = $arrDR;
            continue;
        }
        //
        // NOTE: dateS is sorted thus $sDateS >= $arrLastDR['s']
        //
        if ($arrDR['e'] <= $arrLastDR['e']) {
            continue; // already in the range.
        }
        // --- [e] > lastDR[e] ---
        $sLastDateE_1 = getRelativeDate($arrLastDR['e'], '+1 day');
        if ($arrDR['s'] <= $sLastDateE_1) { // lapping date-range until day+1
            $arrLastDR['e'] = $arrDR['e'];
            continue;
        }

        // there is gap, so need to create new date-range
        array_push($arrMerged, $arrLastDR);
        $arrLastDR = $arrDR;
    }

    if ($arrLastDR === null) {
        array_push($arrMerged, $arrLastDR);
    }
    return $arrMerged;
}

Excel - Copy table to one column

This article copy table to one column, i.e. convert multiple columns into one column!
(after the operation, it also allows to remove empty cells)
also allow to convert multiple columns into more than one column!

This tutorial tested and found working on:
* Microsoft Office Excel 2003 with help of: Microsoft Office Word 2003
* Microsoft Office Excel 2007 with help of: Microsoft Office Word 2007


Instructions (WORKING TUTORIAL):
1. we have an excel file that contains table with data.
2. select all table data and press COPY (Ctrl + C)
3. open new Word document (Tested on Microsoft Office Word 2003)
4. PASTE (Ctrl + V) the copied table into word document.
5. Select the table (in word document) and select MENU: Table > Convert > Table to Text
[Microsoft Office Word 2007: Data (or View, should be last one) Group > Convert to Text]
Table selection using     icon
6. Set a character that not exists in the table (for example |)
7. Keep the text selected and press on MENU: Table > Convert > Text to Table
[Microsoft Office Word 2007: Insert Group > Table > Convert Text to Table]
8. Set number of columns = 1
9. Notice that the character is still | (that we selected)
10. Press OK
11. Now we have table with 1 column (and it is selected!)
12. Select entire table and press COPY (Ctrl + C)
Table selection using     icon
13. Open new EXCEL file (or use the original file)
14. PASTE (Ctrl + V) the table.
15. NOW you have 1 Column table!!!

Hope it helped you...
:)