Help With 2 Formulas

Sloppyjo3

New Member
Joined
Jan 17, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Good day,

Let's say each student at a school is assigned a student ID (Student #) and they can choose to eat either pizza or a hamburger each day. They submit a form requesting what they want for lunch or if they want to switch from one item to the other. I need two formulas, and I'm drawing a mental blank - I want to find out the amount of days between the "start date" and the "form received" date expressed as days (i.e. 27 days) and I also want to find out how often students are switching from pizza to hamburgers. Any help would be greatly appreciated...

Student #Start DateEnd DateForm ReceivedLunch Choice
1​
08/13/201212/31/999905/29/2013Pizza
2​
10/22/201901/19/202010/29/2019Hamburger
3​
04/02/201712/31/999904/07/2017Hamburger
4​
04/12/202012/31/999909/29/2020Pizza
5​
04/08/202007/29/202004/17/2020Hamburger
5​
07/30/202012/31/999908/07/2020Pizza
6​
02/20/201212/31/999905/22/2012Pizza
7​
04/01/201303/26/201405/09/2013Pizza
7​
03/27/201403/31/201504/03/2014Hamburger
7​
04/01/201512/31/999905/13/2015Pizza
8​
04/01/201607/24/201905/04/2016Pizza
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
change the dates to your preference.
N.B. You can paste the information below to your sheet. Click on the icon below the f(x) in the header and paste to your sheet.
Please post extracts of your data with the forum's tool XL2BB so that we do not have to retype or convert the information.

T202109a.xlsm
ABCDEFG
1Student #Start DateEnd DateForm ReceivedLunch ChoiceDays # changes - Lunches
214113441423Pizza2890
324376043767Hamburger70
434282742832Hamburger50
544393344103Pizza1700
654392943938Hamburger9 
754404244050Pizza81
864095941051Pizza921
974136541403Pizza38 
1074172541732Hamburger7 
1174209542137Pizza422
1284246142494Pizza331
3c
Cell Formulas
RangeFormula
F2:F12F2=D2-B2
G2:G12G2=IF(A2=A3,"",SUMPRODUCT(--(A$2:A2=A1),--(1-($E$2:E2=E1))))
 

Forum statistics

Threads
1,144,364
Messages
5,723,939
Members
422,527
Latest member
TotalBeginner201

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