I am trying to create a spreadsheet in Excel that basically help me on counting my total daily allowance and takes account on leap years and number of user-defined "number of holidays" (where I don't get my daily allowance) per month, and also calculate it either in a year or in a certain time period (i.e. January to March).

I have a table in a different sheet which defines the month and number of days, i.e. January has 31 days, March has 31 days, etc.

Therefore, is there an automated way to calculate the total of days selected based on user selection of a time period (i.e cell one defines the starting month, and then cell two define the ending month) from a table of data described above?

  • Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed – DavidPostill Mar 23 '15 at 14:15
  • It isn't really clear exactly what you're trying to do. For example, what time period does the allowance cover (daily, weekly, etc.)? How do leap year and holidays figure in (you get additional allowance, allowance has to stretch farther, you don't need to use the allowance)? What determines whether days other than month length are added or subtracted? Will this deal only with whole months? Are weekends a factor (e.g., there is a workday function that can handle holidays, weekends, etc.)? Is the allowance a relevant factor, or are you just counting days? – fixer1234 Mar 23 '15 at 18:19

NETWORKDAYS or NETWORKDAYS.INTL will calculate the number of 'work days' given a start date (say in B1), end date (say in B2) and list of holidays (say in B4:B40). The holidays could be public holidays or other days off when the allowance is not paid.

The solution is then either: NETWORKDAYS(B1,B2,B4:B40) OR NETWORKDAYS.INTL(B1,B2,1,B4:B40)

(The third term - ,1, - in NETWORKDAYS.INTL allows the "weekend" to be set more easily. Excel should give a pop up window explaining the different options. NETWORKDAYS() assumes it is Saturday and Sunday)

There's plenty of tips on how to use this online, here's two: excel-networkdays-function, excel networkdays.intl function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.