Complex Date/Time Working Days & Hours

[shemayisroel]

Hi All,

Sample data below.

I've managed to calculate the working hours between two date/time based on the working hours defined in cell C2 & D2.

Can't seem to figure out how to exclude Sat/Sum from the equation...

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Start Date/Time</TD><TD style="FONT-WEIGHT: bold">End Date/Time</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Bus Hrs. - Start</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Bus Hrs. - End</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Time calculation</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">24/03/2011 8:00</TD><TD style="TEXT-ALIGN: right">25/03/2011 10:00</TD><TD style="TEXT-ALIGN: center">8:30:00</TD><TD style="TEXT-ALIGN: center">17:00:00</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">10:0</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=(INT(B2)-INT(A2))*(D2-C2)+MEDIAN(D2,C2,MOD(B2,1))-MEDIAN(D2,C2,MOD(A2,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[shemayisroel]

Anyone have any suggestions?

 

[T. Valko]

Anyone have any suggestions?

It looks like the start time is outside of business hours so that makes it a lot more complicated.

Try this...

=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2)

Format as [h]:mm

 

[shemayisroel]

Thanks, I'll give that a go and report back

 

[shemayisroel]

It looks like the start time is outside of business hours so that makes it a lot more complicated.

Try this...

=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2)

Format as [h]:mm

Tested and it worked - MANY THANKS!

 

[T. Valko]

Tested and it worked - MANY THANKS!

You're welcome. Thanks for the feedback!