# FORMULA BASED ON DATE

#### mcintoshmc

##### Active Member
I HAVE A FORMULA THAT WILL GIVE ME THE PERCENTAGE OF PEOPLE THAT ENROLL INTO SCHOOL. IT'S BASED ON THE NUMBER OF INTERVIEWS, AND OUT OF THOSE INTERVIEWS, HOW MANY OF THOSE ENROLL.

THE SPREADSHEET STARTS ON 7/18/07 BECAUSE THAT IS WHEN I STARTED IT. SO, RIGHT NOW IT WILL SHOW ME THE TOTAL PERCENTAGE FROM 7/18 TILL THE DATE OF MY LAST INTERVIEW, BEING THAT I INTERVIEW PEOPLE EVERYDAY, IT WILL SHOW 7/18 TO TODAY(). WHAT FORMULA CAN I USE THAT WILL SHOW ME THE PERCENTAGE FROM WHATEVER DATE I WANT? FOR EXAMPLE:

A2 WILL HAVE THE DESIRED DATE, AND A3 WILL ALSO SHOW ANOTHER DATE. A4 WILL GIVE THE PERCENTAGE.

A2- 8/1/07
A3- 8/24/07
A4- %

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Norie

##### Well-known Member
I have a caps lock button on my computer, and I normally turn it off when posting to forums.

Seriously though what percentage are you actually looking for?

If it's the percentage of pupils enrolled versus those who don't what determines whether or not somebody had enrolled/not enrolled?

#### Scott Huish

##### MrExcel MVP
A2 & A3 are the start end date you want?

Since you didn't provide any sample other than these dates, I'm guessing you have a date, then a column with the number of interviews on this date, as well as the number of people who enrolled on these dates, is this correct?

#### mcintoshmc

##### Active Member
ok, column F is named enrolled. Cell F1 will say either "yes" or "no" student themselves will determine whether they will attend the school I work for. Each day when I interview a student I will add them to the list. Eventually there will be hundreds of students on the list. For example, if I interview 150 students between 7/18/07 and 12/1/07, and only 75 of them enrolled into the school, it will show an enrollment rate of 50%. I want to be able to show what the percentage would be for the month of August for example. Right now I can see what the percentage is from 7/18/07 to 8/24/07. What if I wanted to see what it was from 8/1/07 to 8/24/07?

I want to have two cells that will show both dates, and have the third cell to show the enrollment percentage during that time period.

A1: 8/1/07
A2: 8/24/07
A3: %

#### Scott Huish

##### MrExcel MVP

ADVERTISEMENT

I used Column B for the dates, change as necessary, but note that you can't use full columns with SUMPRODUCT:

Code:
``=SUMPRODUCT(--(B1:B10000>=A1),--(B1:B10000<=A2),--(F1:F10000="Yes"))/SUMPRODUCT(--(B1:B10000>=A1),--(B1:B10000<=A2))``

Format as percent.

#### mcintoshmc

##### Active Member
what goes in A1 and A2 if you used column B for the dates?

#### mcintoshmc

##### Active Member
Oh nevermind, I got it..

#### mcintoshmc

##### Active Member
It worked perfectly--thanks

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,156
Messages
5,835,703
Members
430,380
Latest member
Psalmysam

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

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