Calculating weekdays

[Kathy Smith]

I have a report that has date created in column D and date approved in column F. We have a requirement that the date approved (Column F) cannot be more than 3 business days. How do I write a formula or macro that will count business days (Monday - Friday) only, and return the number of business days it took to approve in an ajacent column (G).

I love your newsletter and your formulas really make my work more efficient.

Thanks

Kathy Smith

 

[SirJB7]

Hi, Kathy Smith!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, you can do either one of these at G2:
=DIAS.LAB(D2;F2) -----> in english: =NETWORKDAYS(D2,F2)
=DIAS.LAB.INTL(D2;F2) -----> in english: =NETWORKDAYS.INTL(D2,F2)

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

Regards!

 

[ismailzkhan]

Hi Kathy Smith,

Use this formula in column G2 =NETWORKDAYS(D3,F3) this gives difference of start and end dates considering weekends.

Hope you got solution

Regards,
Khan

 

[SirJB7]

@ismailzhan
Hi!
If it wasn't for the creative (but wrong) way of using cells from 3rd row to calculate a cell at 2nd row, I'd swear that your formula looks a lot like the 1st of mines.
Regards!

 

[bobhc]

Good day SirJB7
I hope life finds you well old friend

Your formula

=NETWORKDAYS(D2,F2)

Khans formula

=NETWORKDAYS(D3,F3)

as you can see there is a HUGE diffrence....


.

 

[SirJB7]

@b(ut)ob(ut)hc
Hi, my old friendly dog!
Good afternoon, almost still noon.
Yes, I'm scheduling a new visit to my oculist so I can appreciate it better.
Regards!

 

[oldchippy]

@SirJB7,

Hello my friend

Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box,

Just select a cell and then press "fx" key, no need for the "kisses"

 

[SirJB7]

@oldchippy
Hi, my friend!
I was talking about an empty cell, not of one which already had a formula.
Regards!

 

[oldchippy]

@SirJB7,

My mistake I misread it

 

[SirJB7]

@oldchippy
Hi, my friend!
Too much time with b(ut)ob(ut)hc, I guess... you both went together at school?
Regards!

 

[bobhc]

Gooday SirJB7 I hope you are well

I do not misread.......but posters miss certain keys on the keyboard, that I think should be there

 

[SirJB7]

@b(ut)ob(ut)hc
Hi, my even older friend!
Good afternoon.
If I don't misread myself -let me check it again, i.e., for 1st time -, yes, I'd swear to Carlsberg that I never wrote that you missed anything... I was just wondering about your elementary school attendance at the Julian calendar ages (no, I neither said stone nor iron).
Regards!

 

[ThrottleWorks]

Hi Kathy, though @SirJB7 has already provided the required solution, would like to add one point.

We used similar formula in our process, but the only change was, we were required to consider "Public Holidays" also.

I have edited SirJB7's formula to include holiday list, may be you do not need it but just incase ..