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