Help with Formula to Calculate Days Passed

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
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.

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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
Joined
Feb 1, 2017
Messages
16
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.
 

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top