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

Networkdays #NUM error with multi countries and holiday

ALPESH1000

New Member
Hi All

Column AC = incoming date/time
Column AD = handled date/time
Column AF = Service Center Start time e.g., 09:00
Column AG = Service Center end time e.g., 18:00

Column AI I'm trying to calculate working hours between AC and AD. The problem is for India, the center works weekends (it's the only one that works weekends, during the week a few days are 9-9 and some are 9-6).

My formula works OK for everything except India (i.e., the first part of the below formula is giving the error). I also have a separate table (Country holidays) where the Headers is being matched with [Country of sale] field. Is anyone able to help?

Example data: Country = India, incoming date/time = 27/11/2021 07:34, handled date/time = 27/11/2021 09:56, service center start time 09:00, service center end time 18:00. This date is a Saturday, so the expected result in Column AI should be 0:56:00 (format [h]:mm:ss) but for everything in India i'm getting #NUM error.


Formula in AI:

=IF([@[Call Center (Corrected)]]="INDIA",
(NETWORKDAYS.INTL(AC13466,AD13466,0,INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0)))-1)*(AG13466-AF13466)
+IF(NETWORKDAYS.INTL(AD13466,AD13466,0, INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0))),MEDIAN(MOD(AD13466,1),AG13466,AF13466),AG13466)
-MEDIAN(NETWORKDAYS.INTL(AC13466,AC13466, 0, INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0)))*MOD(AC13466,1),AG13466,AF13466),

(NETWORKDAYS(AC13466,AD13466,INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0)))-1)*(AG13466-AF13466)
+IF(NETWORKDAYS(AD13466,AD13466, INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0))),MEDIAN(MOD(AD13466,1),AG13466,AF13466),AG13466)
-MEDIAN(NETWORKDAYS(AC13466,AC13466, INDEX(Country_Holidays,,MATCH([@[Country of Sale]],Country_Holidays[#Headers],0)))*MOD(AC13466,1),AG13466,AF13466))
 
Back
Top