Help

[Dave08]

I am trying to calculate the hours between 22:00 and 08:00.
The e.g. below shows a worker finishes work at 14:00pm on 1/10/2008 and
starts work at 12:00pm on 2/10/2008 the next day, so their is 10 hours
between 22:00(10Pm) and 08:00(8am. The formula below does not seem to bring
back the correct result.


=IF(M46="","0",MAX($K$40-$M46,0)+MAX($K$39-$O45,0))
Result = 8 should be 10
Cell K39 = 22:00:00
Cell K40 = 8:00:00
Cell O45 = 14:00:00 1/10/2008
Cell M46 = 12:00:00 2/10/2008

 

[Sheeloo]

Enter this in any cell
=(M46-O45)*24
and format that cell as number
You will get the result as 22.00

 

[Dave08]

That formula will bring back the total hours between the finish and start. I
only want the hours between 10pm and 8am.

 

[Sheeloo]

Try
=((MIN(K40,O45-ROUND(O45,0)+1)) - (MAX(K39,M46-ROUND(M46,0)+1)) + 1)*24

 

[Dave08]

Thanks, I have tried this formula it seems to work. There is a problem when I
drag this formula down the coloum, for some reason it stops working, any
ideas??

=((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24

 

[Sheeloo]

You should change the relative references to K39 and K40 to absolute
references as
$K$39 and $K$40... since the cells
Cell K39 = 22:00:00
Cell K40 = 8:00:00 do not change.
By dragging K39 will change to K40 and so on...

If this has answered your qeustion then mark it as answered... so that
others do not try to provide solutions..

 

[Dave08]

I have done, still having the problem???

You should change the relative references to K39 and K40 to absolute
references as
$K$39 and $K$40... since the cells
Cell K39 = 22:00:00
Cell K40 = 8:00:00 do not change.
By dragging K39 will change to K40 and so on...

If this has answered your qeustion then mark it as answered... so that
others do not try to provide solutions..

 

[Sheeloo]

Pl. paste the formula you are getting in the new row...

Where are the next set of values? After M46 and O45? Should be in M47 and
O46...

 

[Dave08]

Thats correct, M47 and O46

Pl. paste the formula you are getting in the new row...

Where are the next set of values? After M46 and O45? Should be in M47 and
O46...

 

[Sheeloo]

You should not get an error. Pl. make sure that M47 and O46 contain a valid
date and time...

Just to test copy the values in M46 and O45 to M47 and O46...

 

[Dave08]

Seemsto be a problem. its returns a new value. 9 instead of 10

 

[Sheeloo]

I entered this in J43 with the values provided by you in O45 (1/10/2008 14:00
) and M 46 (2/10/2008 12:00)

=((MIN($K$40,O45-ROUND(O45,0)+1)) - (MAX($K$39,M46-ROUND(M46,0)+1)) + 1)*24
and copied to J44 after copying O45 to O46 and M46 to M47 to get this
=((MIN($K$40,O46-ROUND(O46,0)+1)) - (MAX($K$39,M47-ROUND(M47,0)+1)) + 1)*24

I am getting 10 in both cases.

See what is different at your end. I tested both with Excell 2003 and 2007
on Windows XP.

Pl. note that 10 is decimal 10 and not 10:00 hours... since we have
multiplied the result with 24

I can send the sheet I have if you can send me an email.

To get my email id paste the following in an Excel cell and press Enter...
="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"

 

[Dave08]

I have found the issue, the result it is displaying is the hours from 08:00.

THANKS FOR HELP

 

[Dave08]

=10-((MIN($K$40,O60-ROUND(O60,0)+1)) - (MAX($K$39,M61-ROUND(M61,0)+1)) + 1)*24

This formula worksif the result is<10