number or working days

  • Thread starter Thread starter Gibbyky2
  • Start date Start date
G

Gibbyky2

Jacob kindly gave me the following formula for working out number of working
days between 2 dates. it is exactly what i was looking for but it seems to
calculate a day less

formula
=(C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))

where c16 is 17th july 2009 and B16 is 13th july 2009

the formula works out 4 days but it should be 5 days

i am also looking to calculate the number of days (in this case 5) by 7.4

many thanks
 
The difference is that between subtracting two numbers and counting between
two numbers (the difference between 1 and 5 is 4, but there are 5 numbers to
be counted); or, in other words, the difference is in whether you count both
end days or not. Anyway, the fix is easy... just add 1 to Jacob's formula
(that is, put +1 at the very end of what you are now using.
 
it is exactly what i was looking for but
it seems to calculate a day less
Click to expand...

Then that means it *isn't* exactly what you were looking for ! <g>

I see in your other post that you want to avoid using the NETWORKDAYS
function.

If the formula is consistently off by 1 day then just add 1 to the result.

What about holidays? Do you need to exclude any holidays?

This formula will count the weekdays Monday through Friday between 2 dates
(inclusive):

=SUM(INT((WEEKDAY(B16-{1,2,3,4,5},2)+C16-B16)/7))
 
Thanks rick

that worked great, now when i copy the formula down a range of cells iit
shows 7.4 when the date cells are blank.
can you advise how i hide value in one cell if other 2 cells are blank

ie: b14 and c14 usually have dates and d15 has formula/result. if a1 and a2
have no dates i would like a3 to be blank.
the formula i am now using is as follow
=((C14-WEEKDAY(C14,2)+WEEKDAY(B14,2)-B14)/7*5-MIN(5,WEEKDAY(B14,2))+MIN(5,WEEKDAY(C14,2))+1)*7.4
 
No, don't do that. If the start date is a weekend it counts one to many.
Test for it

=(C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))+(WEEKDAY(B16,2)<5)

Personally, I would use

=C16-B16+1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B16&":"&C16)),2)>5))
 
=IF(OR(B16="",C16=""),"",((C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))+(WEEKDAY(B16,2)<5))*7.4)
 
Back
Top