number or working days
 

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

number or working days
 

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.

--
Rick (MVP - Excel)


"Gibbyky2" wrote in message
...
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

number or working days
 

it is exactly what i was looking for but
it seems to calculate a day less

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))

--
Biff
Microsoft Excel MVP


"Gibbyky2" wrote in message
...
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

number or working days
 

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 follows
=((C14-WEEKDAY(C14,2)+WEEKDAY(B14,2)-B14)/7*5-MIN(5,WEEKDAY(B14,2))+MIN(5,WEEKDAY(C14,2))+1)*7.4

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Gibbyky2" wrote in message
...
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

number or working days
 

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))+(WEEKD AY(B16,2)<5)

Personally, I would use

=C16-B16+1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B16&":"&C16)),2)5))

--
__________________________________
HTH

Bob

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Gibbyky2" wrote in message
...
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

number or working days
 

=IF(OR(B16="",C16=""),"",((C16-WEEKDAY(C16,2)+WEEKDAY(B16,2)-B16)/7*5-MIN(5,WEEKDAY(B16,2))+MIN(5,WEEKDAY(C16,2))+(WEEKD AY(B16,2)<5))*7.4)

--
__________________________________
HTH

Bob

"Gibbyky2" wrote in message
...
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 follows
=((C14-WEEKDAY(C14,2)+WEEKDAY(B14,2)-B14)/7*5-MIN(5,WEEKDAY(B14,2))+MIN(5,WEEKDAY(C14,2))+1)*7.4

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Gibbyky2" wrote in message
...
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

number or working days
 

Hello,

See my date page for some additional explanation:
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd