networkdays

[Guest]

Hi

Hoping someone can help me - I've got access to the "networkdays" function,
but am trying to calculate the number of hours elapsed, where working day is
from 08:00 to 18:00 and start time is cell J2 (1/10/2004 2:25:00) , end date
and time is cell AJ3 (04/10/2004 17:02:00). Should return something like
12:37. However, it doesn't seem to like the date/time format in the same
cell.

 

[Biff]

Hi!


Here's one way if the workday hours are from 8:00 to 18:00
and the times used *will not* be outside of that range:

=SUM(18/24-MOD(J2,INT(J2)),MOD(AJ3,INT(AJ3))-8/24)+
(NETWORKDAYS(J2,AJ3)-2)*10/24

Format the cell as [h]:mm

If your expected answer is 12:37, then the date format
you're using must be dd/mm/yyyy. Also 2:25 must be 2:25 PM
although you used a different time format for the end
date/time.

The reason I bring this up is because making those
assumptions is the only way I could get the formula to
return a value of 12:37.

Also, this formula does not account for holidays. If
holidays might be a factor see Excel help on NETWORKDAYS
to see how to handle holidays.

Biff

---Original Message-----

 

[Guest]

Hi Biff

Thanks for your help - however, this doesn't seem to work when the elapsed
time goes over 24 hours - probably something to do with formatting - trying
to calculate time elapsed within a service calendar 0800-1800 Mon-Fri
(therefore don't want to count weekends, and only count time wiithin M-Fr
0800 -1800) - if date time is 1/10/04 13:24 and close of 5/10/04 14:03 -
should return an elapsed time of 20hours, 39 minutes. (2/10 AND 3/10 being
weekend days, 4/10 being a full 10 hours) - it appears to be returning 10:39
and not counting the hours on 4/10.

I would appreciate any help with this.

Hi!


Here's one way if the workday hours are from 8:00 to 18:00
and the times used *will not* be outside of that range:

=SUM(18/24-MOD(J2,INT(J2)),MOD(AJ3,INT(AJ3))-8/24)+
(NETWORKDAYS(J2,AJ3)-2)*10/24

Format the cell as [h]:mm

If your expected answer is 12:37, then the date format
you're using must be dd/mm/yyyy. Also 2:25 must be 2:25 PM
although you used a different time format for the end
date/time.

The reason I bring this up is because making those
assumptions is the only way I could get the formula to
return a value of 12:37.

Also, this formula does not account for holidays. If
holidays might be a factor see Excel help on NETWORKDAYS
to see how to handle holidays.

Biff

---Original Message-----

Hi

Hoping someone can help me - I've got access to the "networkdays" function,
but am trying to calculate the number of hours elapsed, where working day is
from 08:00 to 18:00 and start time is cell J2 (1/10/2004 2:25:00) , end date
and time is cell AJ3 (04/10/2004 17:02:00). Should return something like
12:37. However, it doesn't seem to like the date/time format in the same
cell.
.

 

[Myrna Larson]

Biff's formula returns 20:39 for me when the cell is formatted as [h]:mm

BTW, it can be simplified a bit, to

=SUM(18/24-MOD(J2,1),MOD(AJ3,1)-8/24)+(NETWORKDAYS(J2,AJ3)-2)*10/24

i.e. replace INT(J2) and INT(AJ3) with 1.

Hi Biff

Thanks for your help - however, this doesn't seem to work when the elapsed
time goes over 24 hours - probably something to do with formatting - trying
to calculate time elapsed within a service calendar 0800-1800 Mon-Fri
(therefore don't want to count weekends, and only count time wiithin M-Fr
0800 -1800) - if date time is 1/10/04 13:24 and close of 5/10/04 14:03 -
should return an elapsed time of 20hours, 39 minutes. (2/10 AND 3/10 being
weekend days, 4/10 being a full 10 hours) - it appears to be returning 10:39
and not counting the hours on 4/10.

I would appreciate any help with this.

Hi!


Here's one way if the workday hours are from 8:00 to 18:00
and the times used *will not* be outside of that range:

=SUM(18/24-MOD(J2,INT(J2)),MOD(AJ3,INT(AJ3))-8/24)+
(NETWORKDAYS(J2,AJ3)-2)*10/24

Format the cell as [h]:mm

If your expected answer is 12:37, then the date format
you're using must be dd/mm/yyyy. Also 2:25 must be 2:25 PM
although you used a different time format for the end
date/time.

The reason I bring this up is because making those
assumptions is the only way I could get the formula to
return a value of 12:37.

Also, this formula does not account for holidays. If
holidays might be a factor see Excel help on NETWORKDAYS
to see how to handle holidays.

Biff

---Original Message-----

Hi

Hoping someone can help me - I've got access to the "networkdays" function,
but am trying to calculate the number of hours elapsed, where working day is
from 08:00 to 18:00 and start time is cell J2 (1/10/2004 2:25:00) , end date
and time is cell AJ3 (04/10/2004 17:02:00). Should return something like
12:37. However, it doesn't seem to like the date/time format in the same
cell.
.