whole working days = NETWORKDAYS - 2
time worked on first day = MAX(0, endofday - MAX(starttime, startofday))
time worked on last day = MAX(0, MIN(entime, endofday) - startofday)
total work days = whole working days + INT((time worked on first day + time worked on last day) / (endofday - startofday))
extra time (after total days) = MOD(time worked on first day + time worked on last day, endofday - startofday)
WORKDAY
The function WORKDAY
is used to calculate the date a certain amount of work days before or after the start date - information here
Syntax
WORKDAY(start_date, days, [holidays])
The WORKDAY function syntax has the following arguments:
Start_date - Required. A date that represents the start date.
Days - Required. The number of nonweekend and nonholiday days before
or after start_date. A positive value for days yields a future date; a
negative value yields a past date.
Holidays - Optional. An optional list of one or more dates to exclude
from the working calendar, such as state and federal holidays and
floating holidays. The list can be either a range of cells that
contain the dates or an array constant of the serial numbers that
represent the dates.
Example usage:
=WORKDAY(A2,A3,A4:A6)
- Where A2 is your start date, A3 is the amount of days from this date you wish to add or subtract, and A4:A6 each contain a holiday date to exclude
NETWORKDAYS
Alternatively, the function NETWORKDAYS
is used to calculate the amount of work days between two dates - information here
Syntax
NETWORKDAYS(start_date, end_date, [holidays])
The NETWORKDAYS function syntax has the following arguments:
Start_date - Required. A date that represents the start date.
End_date - Required. A date that represents the end date.
Holidays - Optional. An optional range of one or more dates to
exclude from the working calendar, such as state and federal holidays
and floating holidays. The list can be either a range of cells that
contains the dates or an array constant of the serial numbers that
represent the dates.
Example usage:
=NETWORKDAYS(A2,A3,A4:A6)
- Where A2 is your start date, A3 is your end date, and A4:A6 contain holiday dates you wish to exclude.
.INTL
In case you need this to work in a situation where weekends are not defined the same way (Saturday and Sunday), both of these formulas have a .INTL
function, where they also take a Weekend
argument as the third argument. Note - This is only supported in Excel 2010+. Information on WORKDAY.INTL and NETWORKDAYS.INTL.
This argument would be an integer, referring to these options:
Weekend number Weekend days
1 (default) Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only
This would affect the formulas above as such, specifying Monday only
as your "weekend":
=WORKDAY.INTL(A2,A3,12,A4:A6)
and
=NETWORKDAYS.INTL(A2,A3,12,A4:A6)
Best Answer
I think it is because it includes both the start and the end date in the calculation. So I think you are really close, and just need take your count and minus 1 out of it. Something like this -
Does this make sense?