Calculating Working Hours between days

[Kyriacos Yerou]

Please help me how can i calcaulate working hours between two days.

Working Hours 07:30 - 14:30

e.g. mm/dd/yyyy

start time 01/04/2010 17:34:58

stop time 01/05/2010 08:23:35

 

[Sreekanth]

Dear Barry Houdini,

This is an excellent formula however, when I am trying to use your formula with little addition as below

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"NA",IF(B2 <a2,"end
< A2 ,"End Date earlier than Start Date", YOUR FORMULA HERE))
where A2 has 1-Apr-13 08:38:55 and B2 has 1-Apr-13 17:38:54, Also I have 1-Apr-13 listed in my Holiday.
The result is giving me error in this senario but works well if 1-Apr-13 is not listed in my Holiday. Could you please help.

Many thanks,
Sreekanth</a2,"end>

I can get this working with the following formula but not sure why formula suggested by Barry Houdini's doesn't work. Any thoughts?

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"NA",IF(B2 < A2,"End Date earlier than Start Date",IF(INT(A2)=INT(B2),IF(NOT(ISNA(MATCH(INT(A2),$H$2:$H$247,0))),0,IF(OR(MOD(A2,1)>=$J$3,MOD(B2,1)<=$J$2),0,IF(AND(MOD(A2,1)<=$J$2,MOD(B2,1)>=$J$3),$J$3-$J$2,IF(AND(MOD(A2,1)>$J$2,MOD(B2,1)<$J$3),B2-A2,IF(AND(MOD(A2,1)<=$J$2,MOD(B2,1)<$J$3),(MOD(B2,1))-$J$2,IF(AND(MOD(B2,1)>=$J$3,MOD(A2,1)>$J$2),$J$3-(MOD(A2,1)))))))),IF(INT(B2)-INT(A2)=1,0,NETWORKDAYS(A2+1,B2-1,$H$2:$H$247)*($J$3-$J$2))+IF(NOT(ISNA(MATCH(INT(A2),$H$2:$H$247,0))),0,IF(MOD(A2,1)>=$J$3,0,IF(MOD(A2,1)<=$J$2,$J$3-$J$2,$J$3-(MOD(A2,1)))))+IF(NOT(ISNA(MATCH(INT(B2),$H$2:$H$247,0))),0,IF(MOD(B2,1)>=$J$3,$J$3-$J$2,IF(MOD(B2,1)<=$J$2,0,(MOD(B2,1))-$J$2))))))

 

[barry houdini]

Hello Sreekanth, this formula works for me:

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"NA",IF(B2 < A2,"End Date earlier than Start Date",(NETWORKDAYS(A2,B2,$H$2:$H$247)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,$H$2:$H$247),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,$H$2:$H$247)*MOD(A2,1),J$3,J$2)))

 

[Sreekanth]

Hello Sreekanth, this formula works for me:

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"NA",IF(B2 < A2,"End Date earlier than Start Date",(NETWORKDAYS(A2,B2,$H$2:$H$247)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,$H$2:$H$247),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,$H$2:$H$247)*MOD(A2,1),J$3,J$2)))

Hi Barry,

Not sure why it was not working earlier for me but now it is working fine, I did not save the earlier file else I would have loved to send that across to you, also there are no changes made to your formula, must have been some bug causing temporary problem.

Many thanks again for looking into the matter.

Best Regards,
Sreekanth

 

[phokingjuice]

Hi Barry,

I'm trying to use your formula but I get an error message saying the formula I typed contains an error... Please help me!

=(NETWORKDAYS(RC[-29],RC[-23])-1)*(17:00-8:00)+IF(NETWORKDAYS(RC[-23],RC[-23]),MEDIAN(MOD(RC[-23],1),17:00,8:00),17:00)-MEDIAN(NETWORKDAYS(RC[-29],RC[-29])*MOD(RC[-29],1),17:00,8:00)

Much appreciated.

 

[pbornemeier]

.