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

What logic is needed for networkdays.intl when saturday has different hours of operation?

Sunspark

New Member
Hi,

I have a working formula which works perfectly (thank you Chandoo.org), IF every day including Saturday has the exact same hours of operation.

I am using it to calculate the number of minutes spent on a task during the business hours.

However, Saturday is working a 6 hour shift instead of 12 so the formula breaks, and you cannot use networkdays twice, namely one part for mon-fri, and one for saturday only with different hours and then add them together because you don't know for sure that Saturday was the last day, it can absolutely roll over into the following week and get completed during a normal mon-fri.

I'm trying to figure out how to still use the networkdays.intl formula but let Saturday have different hours of operation..

The only thing that comes to mind at this time is that I might need a worksheet with columns for each day of the week and each row is a different date and each cell would contain the maximum number of minutes for that day, and one would have to add up all the cells between a starting and ending date, and then subtract the minutes not present on the last day.. this doesn't seem very practical.

Wondering if anyone has any ideas on what needs to be done.

Thanks!
 
I think an example calculation would be helpful.
At first sight, I do not see what is wrong with your idea of combining two networkdays formulas, one for morning shifts and one for afternoons. Letting
Code:
AM_shifts:   = NETWORKDAYS.INTL(start,end,1)
PM_shifts:   = NETWORKDAYS.INTL(start,end,11)
the hours would be given by
Code:
= 6*AM_shifts + 6*PM_shifts
 
I think an example calculation would be helpful.
At first sight, I do not see what is wrong with your idea of combining two networkdays formulas, one for morning shifts and one for afternoons. Letting
Code:
AM_shifts:   = NETWORKDAYS.INTL(start,end,1)
PM_shifts:   = NETWORKDAYS.INTL(start,end,11)
the hours would be given by
Code:
= 6*AM_shifts + 6*PM_shifts

Hi,

I've attached a workbook with two sample test rows in it. I find it strange how it wants to go negative despite setting the mask for both halves.

Vexing!
 

Attachments

  • saturday minutes test.xlsx
    9.6 KB · Views: 3
I seem to be learning from you when it comes to the NETWORKINGDAYS formula!
This is very slow and laborious, using helper columns all over the place!
 

Attachments

  • saturday minutes test.xlsx
    13.4 KB · Views: 6
Back
Top