Hello, I have a formula which is calculating the number of working hours between two dates, and it works beautfully! However, I need to amend it to include all seven days of the week, not just monday - friday. I thought it would as simple as swapping all of the 'NETWORKDAY' references to just 'DAYS'...but it doesn't work. My formula is as follows:
=IFERROR(IF(COUNTIF(RepairOnly,I2)=0,"",(NETWORKDAYS(AG2,AI2,PublicHolidays)-1)*("17:00"-"08:30")+IF(NETWORKDAYS(AI2,AI2),MEDIAN(MOD(AI2,1),"17:00","08:30"),"17:00")-MEDIAN(NETWORKDAYS(AG2,AG2)*MOD(AG2,1),"17:00","08:30")),"")
The clock should only count between the hours of 08:30-17:00, although sometimes a row of data starts/ends outside of these times, hence the moderations in the formula.
Unfortunately, my work laptop security won't let me upload a file, so I'm unsure if anyone will be able to help without a sample? I feel like it should be a simple thing to adjust this to include all days of the week (same hours), but I just can't do it!
Many Thanks in advance if someone can
=IFERROR(IF(COUNTIF(RepairOnly,I2)=0,"",(NETWORKDAYS(AG2,AI2,PublicHolidays)-1)*("17:00"-"08:30")+IF(NETWORKDAYS(AI2,AI2),MEDIAN(MOD(AI2,1),"17:00","08:30"),"17:00")-MEDIAN(NETWORKDAYS(AG2,AG2)*MOD(AG2,1),"17:00","08:30")),"")
The clock should only count between the hours of 08:30-17:00, although sometimes a row of data starts/ends outside of these times, hence the moderations in the formula.
Unfortunately, my work laptop security won't let me upload a file, so I'm unsure if anyone will be able to help without a sample? I feel like it should be a simple thing to adjust this to include all days of the week (same hours), but I just can't do it!
Many Thanks in advance if someone can
