Help with Formula to Calculate Days Passed

[dandeisel]

Hey everyone,

Thanks for taking the time to read and help me with this Particular formula.

I'm a GSM for 2 Dealerships. I created a spreadsheet to help keep a tally of sales throughout the month along with a "ON PACE" formula to help gauge where we're headed based off the number of sales Divided by the selling days that past and then multiplying the total of Selling days in a month. I was able to figure that formula out but I needed to manually enter the days that passed every month and update it every time i opened the spreadsheet to make updates.

What i need help with is 2 formulas for 2 different cells.

On my spreadsheet I have 2 fields Selling Days and Selling Days Past. After doing some research I have been able to come up with this formula for the amount of selling days in the current month.

=NETWORKDAYS.INTL(Start_Date,END_DATE,[WEEKEND],[HOLIDAYS])

Which looks like this on my sheet - =NETWORKDAYS.INTL(B19,B20,11)

B19 block has the start date of 2/1/17, B20 has end date of 2/28/17, and the 11 references the weekends excluding only Sundays throughout the month. That formula equates to 24 (Selling Days in the month of February) I didn’t use any Holidays so I closed the rule with a parenthesis.

? Now the tricky one is the formula for calculating the Selling days that have passed and it should auto calculate correctly excluding Sundays every time the sheet is opened. Keep in mind on February 5th it should calculate 4 selling days passed since the 5th day hasn’t actually passed yet.


I’ve been playing around with a couple different formulas. Just can’t seem get it to to count up towards the end of month (Selling Days that have elapsed but not including the day of). I can get the formulas to count down the amount of Selling days left in the month, but not how many have passed based off the current date and to make sure it skips Sundays.


Thanks again for your help. I know I’m close I just need that one piece of the formula I’m missing.

If anyone needs to see the spreadsheet as a reference let me know and I can email it to you.



<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Arial; -webkit-text-stroke: #000000}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Arial; -webkit-text-stroke: #000000; min-height: 16.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Helvetica; -webkit-text-stroke: #000000; min-height: 17.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 14.0px Helvetica; -webkit-text-stroke: #000000}span.s1 {font-kerning: none; background-color: #ffffff}span.s2 {font-kerning: none}span.s3 {font-kerning: none; color: #ff2600; -webkit-text-stroke: 0px #ff2600}span.s4 {font-kerning: none; color: #942192; -webkit-text-stroke: 0px #942192}span.s5 {font-kerning: none; color: #ff9300; -webkit-text-stroke: 0px #ff9300}</style>

 

[Tetra201]

Maybe

=MAX(0,NETWORKDAYS.INTL(B19,TODAY()-1,11))

 

[dandeisel]

Tetra!! you are awesome! I think it worked. I'll find out tomorrow when I go to open the sheet and the number changes to a 1. It's currently showing a 0 which is correct being today didn't pass yet.

My mac computer on Excel 2016 is able to recognize the =networking.intl function. My computer at work is on Microsoft Excel 2007. When I go to put that function nothing happens. I don't get the rules for that function. Any ideas on why and how to fix that?

Thanks again for your help! Much Appreciated!
Shadi

 

[Tetra201]

This formula should work in Excel 2007:

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(B:B,$B$19):INDEX(B:B,TODAY()-1)))>1))*($B$19< TODAY())

 

[dandeisel]

It didn't work.
Does it make sense that I can't use Excel function =networkdays.intl? is that normal. Do i have to enable something?

Thanks again Tetra for your help and prompt reply.