Date / Time difference using only working hours

[Clarkey]

Hello all.

 

I am trying to create a function that will work out the time difference between 2 times but only includes WORKING HOURS.

This is for a project which tracks the time it takes for a job to be completed.

 

Working hours are 9am - 5pm, Monday - Friday.

 

Example 1

If the job starts at 4:50pm, and somebody looks at the page the next day at 10am, the timer will say 1h 10m.

 

Example 2

If the job starts 6:23pm, and somebody looks at the page the next day at 9:30am, the timer will say 30m 0s.

 

Exmaple 3

If the job starts on Friday at 1:20pm, and somebody looks at the page no Tuesday at 4pm, the timer will say 18h 40m 0s. (It excluded Saturday and Sunday!)

 

I have scoured the internet and cannot work it out. I just logically can't figure out how to approach this. I admit this is past my ability and I am after a clear example which I can learn from.

[Barand]

What formats for the job start and finish times?

[Clarkey]

Hi Barand.

 

The job start time is in UNIX time, and it should compare with the current time.

EDIT: They can obviously be changed into d-m-Y H:i:s easily if needs be.

Edited February 19, 2013 by Clarkey

[Jessica]

No lunch breaks? :-P

 

Here's what I got, it returns the difference in seconds. Then you can do the math to get the hours and minutes. 

 

 

 

Edit: I forgot something hold on.

Edited February 19, 2013 by Jessica

[Jessica]

Here's my fixed attempt:

<?php
function workTime($start, $end){
$endDayOfWeek = date('w', $end);
if($endDayOfWeek == 0 || $endDayOfWeek == 6){
	$end = strtotime('Last Friday 5pm', $end);
} 
$hour = date('H', $end);
if($hour < 9){
	$end = strtotime('-1day 5pm', $end);
}
if($hour > 17){
	$end = strtotime('5pm', $end);
}
$daysBetween = floor(($end-$start)/(60*60*24));
$day = date('w', $start);
$seconds = strtotime('5pm', $start)-$start;
for($i=1; $i<$daysBetween; $i++){
	if($day != 0 && $day != 6){
		$seconds+=(60*60*;
	}
}
$seconds += $end-strtotime('9am', $end);
return $seconds;
}

$start = strtotime('2013-02-14 12:00:00'); // Thursday at Noon
$end = strtotime('2013-02-18 10:45:00'); //Monday at 10am

echo 'Start: ';
echo date('Y-m-d H:i:s', $start);
echo '<br>';

echo 'End: ';
echo date('Y-m-d H:i:s', $end);
echo '<br>';

$seconds = workTime($start, $end);
echo '<br>';
echo '<br>';
echo "$seconds seconds<br>";

$minutes = $seconds/60;
if($minutes > 60){
$hours = floor($minutes/60);
$minutes = $minutes-($hours*60);
echo "$hours hours, ";
}
echo "$minutes minutes";

 

Start: 2013-02-14 12:00:00

End: 2013-02-18 10:45:00

 

81900 seconds

22 hours, 45 minutes

 

I tested it for a few various values and it seemed to work pretty good. It DOES assume your start time is within the right hours. You should be able to add the logic to move the start time to the correct working hours if it's not in them, by looking at this.

[Clarkey]

Jessica, WOW! Thanks very much!

 

I spotted a problem though, use these values...

 

$start = strtotime('2013-02-15 16:50:00');

$end = strtotime('2013-02-18 09:00:00');

 

It should say 10mins, but it says 8 hours, 10 minutes

[Jessica]

for($i=1; $i<$daysBetween; $i++){
       $day++;
       if($day > 6){
           $day = 0;
       }
       if($day != 0 && $day != 6){
           $seconds+=(60*60*;
       }
   }

 

Fix that section.

 

Start: 2013-02-15 16:50:00

End: 2013-02-18 09:00:00

 

600 seconds

10 minutes

Edited February 19, 2013 by Jessica

[Jessica]

By the way, I am by no means claiming this is the best way to do it - it's what I wrote in 10 minutes because it sounded like a fun challenge. Barand may have a much more sophisticated solution coming. 

[Clarkey]

Thanks again Jessica.

 

With these inputs

$start = strtotime('2013-02-19 09:00:00');

$end = strtotime('2013-02-19 10:30:00');

 

I'm expected 1 hours 30 mins but get 9 hours, 30 minutes

[Jessica]

Okay so now it's your turn to try to figure it out. 

[Clarkey]

Okay so now it's your turn to try to figure it out.

 

Let me see...

It seems to be adding another day to it...

[Jessica]

probably need to do an if check before the for($i=1; $i<$daysBetween; $i++){ starts.

[kicken]

Another thing you'll need to consider is how you want to handle holidays.  I assume they would be excluded as well.  You'll need to maintain a list of holdays and their dates in order to exclude them.

 

[Psycho]

A few questions/comments:

 

I think an easier approach would be to just calculate the total seconds between the start and end and then subtract any time needed if the start and and are on different days. Also, how do you need to account for weekends? If the start is on a Friday and the end is on a Monday do you count time for Saturday and Sunday?

[Jessica]

I thought about that Psycho but I think that's more work because you're subtracting weekends and the other 16 hours per day. Mine ignores weekends and adds 8. Not sure if it makes sense.

 

OP said he is not counting weekends. "(It excluded Saturday and Sunday!)"

[Clarkey]

Jessica, I played around with what you provided but still don't understand how it works to be honest so I can't adapt it.

I've spent a few days on this now, and come to the conclusion that I hate dates and times

 

I just can't work out the logic!

Like, how do you know where to start!?

 

Yes, I want any time outside of M-F 9-5 excluded from the calculation, and would prefer the end number to be in seconds so I can calculate the hours, minutes and seconds easier to display it like "16h 25m 44s".

 

Any more input would be appreciated.

[Jessica]

To be fair, I didn't comment it.

 

Here's what my function looks like right now, and it worked for the several examples you've provided.

 

function workTime($start, $end){
$endDayOfWeek = date('w', $end);
if($endDayOfWeek == 0 || $endDayOfWeek == 6){
	$end = strtotime('Last Friday 5pm', $end);
} 
$hour = date('H', $end);
if($hour < 9){
	$end = strtotime('-1day 5pm', $end);
}
if($hour > 17){
	$end = strtotime('5pm', $end);
}
$daysBetween = floor(($end-$start)/(60*60*24));
$day = date('w', $start);

if($daysBetween > 0){
	$seconds = strtotime('5pm', $start)-$start;
	for($i=1; $i<$daysBetween; $i++){
		$day++;
		if($day > 6){
			$day = 0;
		}
		if($day != 0 && $day != 6){
			$seconds+=(60*60*;
		}
	}
	$seconds += $end-strtotime('9am', $end);
}else{
	$seconds = $end-$start;
}
return $seconds;	
}

 

Testing some times

$dates = array(
array('start'=>'2013-02-15 16:50:00', 'end'=>'2013-02-18 09:00:00'), //10 mins
array('start'=>'2013-02-19 09:00:00', 'end'=>'2013-02-19 10:30:00'), //1.5 hr
/*array('start'=>, 'end'=>), // Put more examples in here
array('start'=>, 'end'=>),
array('start'=>, 'end'=>),*/
);




foreach($dates AS $date_set){
$start 	= strtotime($date_set['start']);
$end 	= strtotime($date_set['end']);
echo '<p>Start: '.date('Y-m-d H:i:s', $start).'<br>';
echo 'End: '.date('Y-m-d H:i:s', $end).'</p>';




$seconds = workTime($start, $end);
echo "<p>$seconds seconds</p>";




echo '<p>';
$minutes = $seconds/60;
if($minutes > 60){
	$hours = floor($minutes/60);
	$minutes = $minutes-($hours*60);
	echo "$hours hours, ";
}
echo "$minutes minutes</p>";
}

 

Output:

Start: 2013-02-15 16:50:00

End: 2013-02-18 09:00:00

 

600 seconds

 

10 minutes

 

Start: 2013-02-19 09:00:00

End: 2013-02-19 10:30:00

 

5400 seconds

 

1 hours, 30 minutes

 

I'll go through and add some comments to help explain it.

[Clarkey]

To be fair, I didn't comment it.

I'll go through and add some comments to help explain it.

 

Jessica, your an actual star and little bit of a life saver. It works perfectly.

I'd much rather understand your thought process than just copy your code, so comments would be great!

[Jessica]

There's some bugs in it, but I think it's a starting point anyway.

[Jessica]

<?php
function workTime($start, $end){ 
/*	Adjust End Time	*/	
//If the end time is not a weekday, move it to the end of last week.
$endDayOfWeek = date('w', $end);
if($endDayOfWeek == 0 || $endDayOfWeek == 6){
	$end = strtotime('Last Friday 5pm', $end);
} 
//If the ending hour is outside the work range of 9am-5pm, move it to the acceptable time
$hour = date('H', $end);
if($hour < 9){
	//the previous dat at 5pm
	$end = strtotime('-1day 5pm', $end);
}
if($hour > 17){
	//Today at 5pm
	$end = strtotime('5pm', $end);
}

/*	Adjust Start Time	*/
//If the start time is not a weekday, move it to the start of next week.
$startDayOfWeek = date('w', $start);
if($startDayOfWeek == 0 || $startDayOfWeek == 6){
	$start = strtotime('Next Monday 9am', $start);
} 
//If the starting hour is outside the work range of 9am-5pm, move it to the acceptable time
$hour = date('H', $start);
if($hour < 9){
	//same day at 9am
	$start = strtotime('9am', $start);
}
if($hour > 17){
	//next day at 9am
	$start = strtotime('+1day 9am', $start);
}

/*	Calculate Difference	*/
if(date('Y-m-d', $start) == date('Y-m-d', $end)){
	//The dates are the same day and have been adjusted to be within 9am-5pm, just subtract the times. 
	$seconds = $end-$start;
}else{
	//How many days (rounding down) are between the start time and the new edited end time?
	$daysBetween = floor(($end-$start)/(60*60*24));

	$day = $startDayOfWeek; //We need to track what day of the week we are adding hours for, so we can skip weekends.

	//The seconds for the first day from work started until the end of the day at 5pm
	$seconds = strtotime('5pm', $start)-$start;

	//The rest of the days
	for($i=1; $i<$daysBetween; $i++){
		$day++; //Add a day
		if($day > 6){ //If it's greater than 6 roll back to 0. 
			$day = 0;
		}
		//If it's not a weekend
		if($day > 0 && $day < 6){
			$seconds+=(60*60*; //Add 8 hours worth of seconds.
		}
	}

	//Add the last day's worth of work.
	$seconds += $end-strtotime('9am', $end);
}
return $seconds;	
}

[Clarkey]

There's some bugs in it, but I think it's a starting point anyway.

 

Your right. For example, if you do array('start'=>'2013-02-19 08:45:00', 'end'=>'2013-02-19 10:30:00') it should be still 1.5hr but it does 1.75hr.

[Jessica]

You know what you should probably move the HOUR checks to before the WEEKEND checks. I think?

 

[Psycho]

Here's my attempt. I've tested it a little and it seems to work correctly. It looks like a lot of code, but I added a lot of validation in the code. For example, if the start date is after the end date then a false will be returned.

 

A couple notes:

- If the start and end go over a weekend, no time is allocated for Sat & Sun

- Holidays are supported (i.e. time will not be allocated for holidays), but you will need to provide an array of holidays to pass to the function workDays().

 

There are two functions: workTime() and wordDays().

 

workTime() takes four parameters: the start time and end time in a string format. It optionally takes a begin and end valid times in a float format (so if the work day starts at 8:15 am the value should be 8.25). The function will calculate the total seconds between the start and end date/times. If the two are on the same date then that difference in seconds is returned. If the dates are different, then the function workDays() is called to count the number of work days that the period falls on - weekends and holidays are excluded in that calculation. 2 is subtracted from that result to get the number of days where the full time should be applied. Then the function workTime() will calculate the time on the first partial day, the full workdays, and the last partial day.

 

I'm sure there are a few things I would improve on - especially from an efficiency perspective. Also, if a workday can go through the night this might have problems for days where the clocks adjust for daylight savings time. This is a fully working script with an form for testing. Enjoy:

<?php

error_reporting(E_ALL);

//Set defaults for form fields
$startStr = '2013-02-18 16:30:00';
$endStr = '2013-02-19 09:55:00';

if(isset($_POST['start']) && isset($_POST['end']))
{
   //Set vars to repopulate form fields
   $startStr = $_POST['start'];
   $endStr = $_POST['end'];

   $totalTime = workTime($_POST['start'], $_POST['end']);

   echo "Start: " . date('l, F j, Y H:i:s', strtotime($_POST['start'])) . "<br>\n";
   echo "End: " . date('l, F j, Y H:i:s', strtotime($_POST['end'])) . "<br>\n";
   $hours = floor($totalTime / 3600);
   $minutes = floor(($totalTime-($hours*3600)) / 60);
   echo "Total time: $hours:" . str_pad($minutes, 2, '0', STR_PAD_LEFT);
}

function workDays($startTS, $endTS, $holidays=array())
{
   $dayTS = strtotime('12:00:00', $startTS); //Set to noon to avoid daylight savings problems
   $endDateStr = date('Y-m-d', $endTS);

   $workDays = 1;
   while(date('Y-m-d', $dayTS) != $endDateStr)
   {
    //If day not a weekend or holiday add 1
    if(date('N', $dayTS)<6 && !in_array(date('Y-m-d', $dayTS), $holidays))
    {
	    $workDays++;
    }
    $dayTS = strtotime('+1 day', $dayTS);
   }

   return $workDays;
}

function workTime($startStr, $endStr, $validStartFloat='9.0', $validEndFloat='17.0')
{
   $startTS = strtotime($startStr);
   $endTS   = strtotime($endStr);

   //Verify end time comes after start time
   if($startTS > $endTS) { return false; }
   //Verify start and end times are within valid work times
   $startFloat = floatval(date('G', $startTS) + (date('i', $startTS)/60));
   $endFloat = floatval(date('G', $endTS) + (date('i', $endTS) / 60));
   if($startFloat<$validStartFloat || $startFloat>$validEndFloat) { return false; }
   if($endFloat<$validStartFloat || $endFloat>$validEndFloat) { return false; }

   if(date('Y-m-d', $startTS) == date('Y-m-d', $endTS))
   {
    //Start and end are on same day
    $work_seconds = $endTS - $startTS;
   }
   else
   {
    //Start and end are on different days
    $endOfFirstDaySec = ($validEndFloat - $startFloat) * 3600;
    $begOfLastDaySec = ($endFloat - $validStartFloat) * 3600;
    $fullWorkDays = workDays($startTS, $endTS) - 2;
    $fullWorkDaysSec = $fullWorkDays * ($validEndFloat-$validStartFloat) * 3600;
    $work_seconds = $endOfFirstDaySec + $fullWorkDaysSec + $begOfLastDaySec;
   }

   return $work_seconds;
}

?>
<html>
<head>

</head>
<body>

<br><br>
<form action="" method="post">
Start: <input type="text" name="start" value="<?php echo $startStr; ?>"><br>
End: <input type="text" name="end" value="<?php echo $endStr; ?>"><br>
<button type="submit">Submit</button>
</form>
</body>

</html>

[Barand]

I didn't have time to have a stab at it yesterday .

 

I'm assuming

- jobs in a database

- if a job is completed prior to our querying then the time is from start to completion and not to now

- for test purposes, Feb 15 was a holiday

 

<?php
include("db_inc.php");
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

/*********** DATA *******************************
(jobno, jobstart, jobfinish)
(1, '2013-02-14 1 6:50:00', 0),
(2, '2013-02-15 09:00:00', 0),
(3, '2013-02-18 18:30:00', 0),
(4, '2013-02-18 08:00:00', '2013-02-18 18:00:00')
*************************************************/

function workingHours($db, $job)
{
   $holidays = array('2013-02-15');

   $sql = "SELECT jobstart, IF(jobfinish=0, NOW(), jobfinish) as jobfinish
   FROM job
   WHERE jobno = $job";
   $res = $db->query($sql);
   list($js, $jf) = $res->fetch_row();
   $jobstart = new DateTime($js);
   $jobfinish = new DateTime($jf);

   $inc = DateInterval::createFromDateString('next weekday');
   $dp = new DatePeriod($jobstart, $inc, $jobfinish);

   $totalmins = 0;
   foreach ($dp as $day) {
    $datepart = $day->format('Y-m-d');
    $sod = clone $day;
    $sod->settime(9,0); // start of working day
    $eod = clone $day;
    $eod->settime(17,0);  //end of working day

    if (in_array($datepart, $holidays)) {
	    continue;
    }
    if ($datepart = $jobstart->format('Y-m-d')) {   // first day
	    $t = min(max($sod, $jobstart), $eod);    // ensure time between 9 - 5pm
	    list($h,$m) = explode(':', $eod->diff($t)->format('%h:%i'));
	    $totalmins += $h*60 + $m;
    }
    if ($datepart = $jobfinish->format('Y-m-d')) {   // last day
	    $t = min(max($sod, $jobfinish), $eod);    // ensure time between 9 - 5pm
	    if ($datepart = $jobfinish->format('Y-m-d')) {    // start date = finish date
		    list($h,$m) = explode(':', $eod->diff($t)->format('%h:%i'));
		    $totalmins -= ($h*60 + $m) ;
	    }
	    else {
		    list($h,$m) = explode(':', $sod->diff($t)->format('%h:%i'));
		    $totalmins += ($h*60 + $m) ;			    
	    }
    }
    if ($datepart != $jobstart->format('Y-m-d')
		    && $datepart != $jobfinish->format('Y-m-d')) {   // somewhere inbetween
	    $totalmins += 480;
    }

   }
   return array(
    $js, $jf, sprintf('%d hrs %d mins', $totalmins/60, $totalmins%60)
   );

}

for ($job=1;$job<=5;$job++) {
   $results = workingHours($db, $job);
   vprintf('<p>start : %s<br>finish : %s<br>Job time : %s</p>', $results);
}

/***** RESULTS **************
start : 2013-02-14 16:50:00
finish : 2013-02-20 17:21:50
Job time : 24 hrs 10 mins

start : 2013-02-15 09:00:00
finish : 2013-02-20 17:21:50
Job time : 24 hrs 0 mins

start : 2013-02-18 16:00:00
finish : 2013-02-20 17:21:50
Job time : 17 hrs 0 mins

start : 2013-02-18 08:00:00
finish : 2013-02-18 18:00:00
Job time : 8 hrs 0 mins

start : 2013-02-18 07:30:00
finish : 2013-02-20 17:21:50
Job time : 24 hrs 0 mins

*/
?>

Edited February 20, 2013 by Barand

[Barand]

Yes, I know, I don't check if the job is found in the table (I'd added an extra test case in mine)