Need help with datediff function to exclude weekends

[sleepyw]

I'm very new to PHP and have been using tutorials and help from across the net to create my site. But I can't find a working code to do what I need.

 

I have a table with a start date and end date and I want to calculate the duration between those dates in WORKING days (and exclude public holidays, if possible). I have an HTML form where the user inputs the start date and end date, then I want the php file handling the update to parse the duration field automatically.

 

Here's what I have, which is of course wrong, but maybe it will help explain what I'm trying to do:

 

//$_GET['all the variables from the HTML form'];
$id = $_POST['id'];
$bus_name = $_POST['bus_name'];
$web_url = $_POST['web_url'];
$image_name = $_POST['image_name'];
$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];

$duration = datediff($_POST['end_date'], $_POST['start_date']); //but this needs to only include weekdays (non-public holiday weekdays if possible) 

// update data in mysql database
$sql="UPDATE table_name SET id='$id', bus_name='$bus_name', web_url='$web_url', image_name='$image_name', start_date='$start_date', end_date='$end_date', duration='$duration' WHERE id='$id'";
$result=mysql_query($sql);

 

I can't seem to find anything that works that takes data from a form and calculates it to write to the db.

 

Any help would be very much appreciated.

[sleepyw]

No one? 

[PFMaBiSmAd]

Every 7 days contains 2 weekend days. Take the starting number of days and divide by 7. The integer part of the answer x 2 is the number of weekend days to subtract from the starting number of days. If the remainder of the division is 6, subtract one more weekend day.

 

To subtract out holidays, you would need to find the number of weekday holidays that falls between the start and end dates and subtract that as well. A database of holidays would make make this part easy.

[sleepyw]

Unfortunately, that doesn't help me. I could do the database with the holidays, but the formula for calculating the duration between dates is where I'm lost. Everything online I've found is riddled with errors and datediff() doesn't seem to work just to calculate even total days.

[PFMaBiSmAd]

Here's a query that does this -

SELECT @days:= DATEDIFF('2009-03-05','2009-02-23'),
@wks:= @days DIV 7, @remdr:= @days % 7, @days - (2*@wks) - if(@remdr = 6,1,0) as total;

[sleepyw]

I'm not sure I follow. What's the output of that - total days? Or total days minus weekends?  And I would use my database fields of start_date and end_date in place of the dates you listed, I assume.

 

I'm not sure what I'd do with this when I was done, based on the sample code I posted that I'm trying to produce.

[PFMaBiSmAd]

Actually, the above query does NOT work because it does not take into account if the start date plus the remainder of the division spans part or all of a weekend.

[PFMaBiSmAd]

But here is one that works -

 

 SELECT @start_date:= '2009-02-28', @end_date:= '2009-03-05',
@start_date:= CASE weekday(@start_date) WHEN 5 THEN DATE_ADD(@start_date,INTERVAL 2 DAY) WHEN 6 THEN DATE_ADD(@start_date,INTERVAL 1 DAY) ELSE @start_date END,
@days:= DATEDIFF(@end_date,@start_date), @wks:= @days DIV 7, @remdr:= @days % 7, @endingwkday:=weekday(@start_date) + @remdr,
@correction:= CASE WHEN @endingwkday > 5 THEN 2 WHEN @endingwkday > 4 THEN 1 ELSE 0 END,
if(@days > -1, 5*@wks + @remdr - @correction + 1, 0) as total;

 

Line by line explanation -

 

1) Sets some user variables with the actual start/end dates

2) Adjusts the start date if it falls on a weekend

3) Calculates various numbers

4) Correction factor if the start date + remainder spans part or all of a weekend

5) Produces result (the if() handles end dates that are less than the start date)

[sleepyw]

Thanks for taking the time to write that up - I'll have to play with it over the weekend and see if I can get it to work.

[sleepyw]

Now that I look at that, I'm still clueless. Why does it start with SELECT? What goes in front of that?  There are elements missing and I don't know what they are.

 

If you look at my sample code in the OP, I just need to re-write that. I'm too new to PHP where code snippets don't help because I don't know how to fill in the blanks yet.

 

To do DateDiff(), I need an entire function written, i think. Using simple math functions don't seem to be working on DATE fields (yyyy-mm-dd).

[sleepyw]

Just to clarify - in my original post, I'm trying to calculate the $duration string by calculating the difference between a $start_date and $end_date and subtract weekends, if possible.