=today() ????

[thursday140]

I was wondering what would the formula be if I wanted to incorporate a today formula and divide the amount of jobs open for the remaining days left in the month not including the weekends.
So basically Open Jobs (divided by) Remaining Days left in the month. Example: for March I have 88 Open Jobs and there's only 14 days left in the month of March. How many Jobs will I have to do per day to complete all Open Jobs.
Is there a was to incorporate a today formula so excel can count the remaining days automatically? And if so what would it be? Any advise will be extremely helpful!

[JohnTopley]

This will give number of workdays (excluding Sat/Sun) left after (i.e. not including) TODAY() remaining in current month

=NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

[thursday140]

This will give number of workdays (excluding Sat/Sun) left after (i.e. not including) TODAY() remaining in current month

=NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

That was so awesome! Thank you so much, how would I incorporate a division formula? I want to divide the the 88 open jobs with the remaining working days available.

[JohnTopley]

=A2/NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

where A2 is your "Open Jobs" Value i.e. contains 88

[Jonmo1]

I would recommend NOT subtracting 1 from the result of NetworkDays.
You could end up with erroneous results if the Start/End Dates happen to fall on a weekend/holiday.
Instead, Add 1 to the Start Date OR Subtract 1 from the End Date, depending on which one you want excluded from your count.

=NETWORKDAYS.INTL(TODAY()+1,EOMONTH(TODAY(),0),1)
or
=NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY()-1,0),1)


Also, if you do stick with subtracting 1 from the result of networkdays
=A2/NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

You need parens around the networkdays-1 part
Otherwise it does A2/Networkdays First, THEN subtracts 1 from that result.

=A2/(NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1)

[thursday140]

This will give number of workdays (excluding Sat/Sun) left after (i.e. not including) TODAY() remaining in current month

=NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

That was so awesome! Thank you so much, how would I incorporate a division formula? I want to divide the the 88 open jobs with the remaining working days available.

[Chris McGlothen]

I believe what John is saying is Cell A2 is where you would have the remaining number of jobs for the month typed in. You would simply change A2 with whatever cell you place your total number of jobs located in.

[thursday140]

I would recommend NOT subtracting 1 from the result of NetworkDays.
You could end up with erroneous results if the Start/End Dates happen to fall on a weekend/holiday.
Instead, Add 1 to the Start Date OR Subtract 1 from the End Date, depending on which one you want excluded from your count.

=NETWORKDAYS.INTL(TODAY()+1,EOMONTH(TODAY(),0),1)
or
=NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY()-1,0),1)


Also, if you do stick with subtracting 1 from the result of networkdays
=A2/NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1

You need parens around the networkdays-1 part
Otherwise it does A2/Networkdays First, THEN subtracts 1 from that result.

=A2/(NETWORKDAYS.INTL(TODAY(),EOMONTH(TODAY(),0),1)-1)

Since this has to be done manually on a month to month basis, is there a way to automate this process. Or perhaps a different formaula that can produce the same results 1 month in advance. SO i have an idea as to how many jobs I have to complete 1 month in advance?

[JohnTopley]

@Jonmo1: Thanks for the corrections - I certainly was not aware of the potential issue to NETWORDAYS.INTL.


Re using this on a monthly basis (bearing in mind Jonmo1's advice)

=A2/(NETWORKDAYS.INTL(StartDate,EOMONTH(Startdate,0),1)-1)

where startdate in the first of the month e.e. if you want to prediction for April, then startdate = 01/04/2016 (dd/mm/yyyy)

So if we put the date in B2

=A2/(NETWORKDAYS.INTL(B2,EOMONTH(B2,0),1)-1)

Make sense?

[Jonmo1]

You're still subtracting 1 from the result of networkdays.

Try either
=A2/(NETWORKDAYS.INTL(B2+1,EOMONTH(B2,0),1)) -> if Start date should be excluded from count
or
=A2/(NETWORKDAYS.INTL(B2,EOMONTH(B2,0)-1,1)) -> if End date should be excluded from count

B2 = Start Date.

[JohnTopley]

More likely

=A2/(NETWORKDAYS.INTL(B2,EOMONTH(B2,0),1)

if planning for a future month as you would not exclude any working day ...

But currently it omits holidays!

[thursday140]

@Jonmo1: Thanks for the corrections - I certainly was not aware of the potential issue to NETWORDAYS.INTL.


Re using this on a monthly basis (bearing in mind Jonmo1's advice)

=A2/(NETWORKDAYS.INTL(StartDate,EOMONTH(Startdate,0),1)-1)

where startdate in the first of the month e.e. if you want to prediction for April, then startdate = 01/04/2016 (dd/mm/yyyy)

So if we put the date in B2

=A2/(NETWORKDAYS.INTL(B2,EOMONTH(B2,0),1)-1)

Make sense?

Apologies if question I ask is novice. However, should I be entering the date in place of "B2", or does the formula automatically know B2 is the start date 01/04/16? Or are you saying to enter any Start Date I wish in place of B2, to calculate the amount of jobs I have for the month in question (which in this example it's April)?

[thursday140]

More likely

=A2/(NETWORKDAYS.INTL(B2,EOMONTH(B2,0),1)

if planning for a future month as you would not exclude any working day ...

But currently it omits holidays!

Thank you so much for this formula. However am I entering a date in B2 or was that just an example as to what B2 represents?

[Jonmo1]

B2 is a cell where you enter a date, any date of your choice.