NEED HELP!

[bbaumann]

Hello,

I am trying to create an excel workbook which allows me to enter a specified date and calculate hours. Basically there is a limit to working days within a month that i can use. Ex 21 days in aug and so on. If i do =networkdays and get a number from one date to another (7/1/2020- 6/5/2021 is 243 days), i need it to manipulate a monthly breakdown to give me allotted hours. If someone is allotted 30 hours a week, and there are specified working days (m-f) each month, how many monthly hours do they get. I have made something near what im looking for but i want to type in a start and end date and have it manipulate the Monthly hours on its own

[#2]

@bbaumann 

If you manipulate the dates, it changes the monthly hours

[#3]

Hello @bbaumann ,

for such calculations, Excel keeps easy-to-use time intelligence functions handy. The formula you are looking for is NETWORKDAYS.INTL (Link). You fill it with the parameters start- and end-date, a parameter for the weekend days (1 for Sat+Sun), and an extra parameter for the amount of public holidays within the timeframe.

For instance, using 01.07.2020 and 31.07.2020, Sat+Sun (1) and no public holidays, the result is 23 working days.

Is that the function you are looking for?

Kind regards

Jan

[#4]

Hello again @bbaumann ,

I must apologize, I failed to correctly look into your file. It does seem to be calculating correctly, but apparently there is an issue with the field value formatting. Other than that perhaps when testing you manipulated the days cutting out weekends and the number of working days stayed the same, i.e. changing 31.01.21 to 30.01.21 to 29.01.21 will get you the same result since the 30. and 31.01. are saturday/sunday. Anyhow, I solved the field formatting issue by simply changing the G-Column ("Total working days") from Standard to Number. On Standard it kept on flipping the value to Date when manually recalculating. Other than that it was working fine for me.

Kind regards

Jan