Work Hours Calculation

[jawaharprm]

Hi Team,

Please help this formula,

Need to calculate correct work hours by date & time tat is 2 hours from allocation date and closing date

Ex - 11/9/2021 12:34:06 AM - 11/10/2021 7:34:00 AM work hours should by 1 hours bzc after 1AM shift it closed and Saturday & Sunday should not consider

i used below mentionedformula but not getting correct hours.

=IF(A12="","",(NETWORKDAYS(A12,D12)-1-MOD(A12,1)+MOD(D12,1))*24)

IF(MOD(A11-D11,1)*24>8,MOD(A11-(D11+8/24),1)*24-(A11<(D11+8/24))*(22-6)-MEDIAN(A11*24,22,6)+MEDIAN((D11+8/24)*24,22,6),0)

sample file enclosed for your reference

Thanks
Jawahar

 

[vletm]

jawaharprm
Your Need to calculate correct work hours by date & time tat is 2 hours from allocation date and closing date
Where are Your expected correct working hours?
Here, one sample to do same kind of results by using times.

 

[jawaharprm]

Hi Sir,

Sample is updated and i have enclosed sample file also..

 

[jawaharprm]

Hi Sir,

Pls help to slove the formula error

=(NETWORKDAYS(B3,C3)-1)*("7.00"-"4.00")+IF(NETWORKDAYS(C3,C3),MEDIAN(MOD(C3,1),"7.00","4.00"),"7.00")-MEDIAN(NETWORKDAYS(B3,B3)*MOD(B3,1),"7.00","4.00")*(NETWORKDAYS(B3,C3)-1)*("14.00"-"23.00")+IF(NETWORKDAYS(C3,C3),MEDIAN(MOD(C3,1),"14.00","23.00"),"14.00")-MEDIAN(NETWORKDAYS(B3,B3)*MOD(B3,1),"14.00","23.00")*(NETWORKDAYS(B3,C3)-1)*("16.00"-"1.00")+IF(NETWORKDAYS(C3,C3),MEDIAN(MOD(C3,1),"16.00","1.00"),"16.00")-MEDIAN(NETWORKDAYS(B3,B3)*MOD(B3,1),"16.00","1.00")

 

[vletm]

jawaharprm
Your newer sample is basic same
--- except You took some columns away.
Seems You skipped to show expected correct results.
... without those - it's a challenge to help.

About Your solve the formula error
I won't use that kind of ...
Did You as well as skip my sample formulas?

 

[jawaharprm]

Hi Sir,

I your previous mail there is no sample formula.

 

[vletm]

jawaharprm
A sample is sample until You could let others know - what do You really would like to get?
I added that my sample.