• 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.

Calculating Exact Time taken considering sunday and working hours

ismailzkhan

ismailzkhan

Member
Dear Techies,

Please help me in calculating Exact Time taken considering Sunday and working hours. Our working hours are 09:30 to 18:00. Check example below:

A1 is 11-22-13 2:12 PM and in B1 11-24-13 5:54 PM, I need difference of these two days and times considering a sunday and my working hours

Regards,
I Khan
 
Try: =24*((INT(B1-A1)+1)-(INT(B1-A1)*(TIME(21,30,)-TIME(6,,)))-(A1-INT(A1))-(INT(B1)+1-B1))
Format the cell as [h]:mm
 
I interpreted the question differently...
If you wish to consider Sundays as your weekend, and as such ignore any hours worked on Sundays, then try:
=NETWORKDAYS.INTL(A1,B1,11)*8.5-24*((MOD(A1,1)-"9:30 am")+("6 pm"-MOD(B1,1)))
or
=NETWORKDAYS.INTL(A1,B1,11)*8.5-24*((TEXT(A1,"h:m")-"9:30")+("18:00"-TEXT(B1,"h:m")))

NETWORKDAYS.INTL was introduced in Excel 2010 I believe.

To convert an amount in decimals to a fraction of an hour, you could use
=DOLLARFR(D1,60)
where D1 contains the result from one of the above formulas.

Cheers,
Sajan.
 
Dear Hui and Sajan,

Thanks for replying. I tried formulas suggested by you but actual result I’m not getting.

Considering A1 is 22-11-2013 9:00:00 AM and B1 is 27-11-2013 5:00:00 PM and formula of Mr. Hui =24*((INT(B1-A1)+1)-(INT(B1-A1)*(TIME(21,30,)-TIME(6,,)))-(A1-INT(A1))-(INT(B1)+1-B1)) is giving value of 12:00 and Mr. Sajan formula gives value 42:00. If I calculate manually time comes as 41:30 (considering Sunday and working hours 9:30 to 18:00).

Please Help!!

Regards,

I Khan
 
Hi ismailzkhan,

A little modification on Sajan's formula:
PHP: 
=NETWORKDAYS.INTL(IF(MOD(A1,10)<1/48*19,INT(A1)+(1/48*19),A1),B1,11)*8.5-24*((MOD(IF(MOD(A1,10)<1/48*19,INT(A1)+(1/48*19),A1),1)-"9:30 am")+("6 pm"-MOD(B1,1)))
 
Hi I Khan,
There was logical error in the formula I supplied, where times earlier than 9:30am were not being accounted for. Try:
=NETWORKDAYS.INTL(A1,B1,11)*8.5-24*((MAX(MOD(A1,1), "9:30 am")-"9:30 am")+("6 pm"-MIN(MOD(B1,1), "6 pm")))

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top