# Networkdays function not working?

1. ## Networkdays function not working?

I'm trying to calculate the number of weekdays between 1-27-2013 (cell h3) and 6-11-2013 (cell L3). I have the formula =networkdays(H3,L3) with no holidays. the problem is when I format the result cell as a number it gives me 97. When I try to format as dd:hh:mm it gives me 06:00:00. Can someone please explain this and how I can correct it so the format reads 97:00:00?

Thank You!

2. ## Re: Networkdays function not working?

It won't work as you want.

The dd format will only work up to the number of days in a specific month.

The reason you're getting 6:00:00 is because the number 97 is the date serial number for Apr 6 1900.

The best you can do is have the formula return the TEXT string 97:00:00.

3. ## Re: Networkdays function not working?

I've tried =text(NETWORKDAYS(H3,L3), dd:hh:mm), but this doesn't seem to work either. Is it not right?

4. ## Re: Networkdays function not working?

welcome to the forum. as Tony mentioned, using dd wouldn't work at all. have you tried formatting dates to a custom format before? say you have 1jan2013
d-mmm-yy
will show
1-Jan-13

dd-mmm-yy
will show
01-Jan-13

so the furthest it can go to is 31. and like what Tony mentioned, the number 97 actually represents a Date if you format as such. 1 is 1 Jan 1900. 2 is 2 Jan 1900. so when it reaches 97, it is 6 Apr 1900. so dd-mmm-yy will give you
06-Apr-00

NETWORKDAYS is not going to give you hours & minutes, so your last 2 sets of 0s are always 0s. so maybe just:
=TEXT(NETWORKDAYS(H3,L3),"00")&":00:00"

5. ## Re: Networkdays function not working?

Thanks for your responses. Neither worked. also I'm guessing that since I'm trying to format text that I won't be able to perform math functions. I may just have to deal with formatting as a number.

Again thank you for your help.

6. ## Re: Networkdays function not working?

Originally Posted by sherringtonjr
I'm guessing that since I'm trying to format text that I won't be able to perform math functions.
benishiryo's formula returns a TEXT string so yes, you are correct.

Good luck!

7. ## Re: Networkdays function not working?

You can continue to use the standard networkdays function to return 97

Apply a custom format of

##":00:00"

8. ## Re: Networkdays function not working?

you could multiply it by 10000 then custom format as
00\:00\:00 remembering to divide again by 10000 in subsequent calculations

9. ## Re: Networkdays function not working?

Originally Posted by Jonmo1
You can continue to use the standard networkdays function to return 97

Apply a custom format of

##":00:00"
Good thinking!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1