# Calculating Exact Time taken considering sunday and working hours

#### 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

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

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)))``

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

• ismailzkhan and Xiq
@Sajan
Nice use of MAX and MIN function!

• Sajan
Thanks Xiq! They seemed the straightforward choice! Dear Sajan & Xiq,

Thanks a ton for helping me out. Ur formulas works

Regards,
I Khan

Thanks for the feedback I Khan! Happy to help! Welcome back anytime!