• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Work Hours Calculation

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
 

Attachments

  • hours.xlsx
    11 KB · Views: 7
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.
 

Attachments

  • hours.xlsx
    11.5 KB · Views: 7
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")
 

Attachments

  • hours.xlsx
    10.5 KB · Views: 4
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?
 
Back
Top