CODEWITHSUNDEEP
vbaexcel
LearningNew
LearningNew

Reputation: 1429

Excel Macro to calculate number of working days/hours for each month

I have a start date and end date in two columns. There are 12 other columns one for each month. I am trying to run a macro which gives the number of working hours/days for each month in those 12 columns based on the start and end date

Upvotes: 0

Views: 3264

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Assuming you have holidays on saturdays and sundays, you can try the below formula,

=NETWORKDAYS(A2,B2,$G$2:$G$4)

Where G2:G4 is the holiday range. This is optional. enter image description here

Excel has another function which allows you to alter your weekends,

=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])

You can specify weekends and holidays as well. The below formula indicates that all days in a week are working days, (column E)

=NETWORKDAYS.INTL(A2,B2,"0000000")

If you have only 8 working hours a day, just multiply the difference with 8. Hope this helps

Upvotes: 2

David Rushton
David Rushton

Reputation: 5030

You can use the NetworkDays formula for this.

=NETWORKDAYS(StartDate, EndDate, [Holidays])

If you don't want Excel to count bank holidays you will need to supply the formula with a list.

The number of working hours in a day isn't standard. You will need to calculate this yourself (Working Days * Working Hours in Day).

Upvotes: 0

Related Questions