# Help with Formula to Calculate Days Passed

#### dandeisel

##### New Member
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>

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Tetra201

##### MrExcel MVP
Maybe

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

#### dandeisel

##### New Member
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!

#### Tetra201

##### MrExcel MVP
This formula should work in Excel 2007:

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

Last edited:

#### dandeisel

##### New Member
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?

Replies
0
Views
168
Replies
2
Views
381
Replies
11
Views
2K
Replies
1
Views
263
Replies
2
Views
383

1,130,155
Messages
5,640,448
Members
417,143
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back