• 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

jawaharprm

New Member
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

vletm

Excel Ninja
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

jawaharprm

New Member
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

vletm

Excel Ninja
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?
 
Top