Subtracting months and days in Excel

Tigger8

New Member
Joined
May 21, 2015
Messages
1
I have a situation at work in which the staff must take a given number of months and sometimes days and reduce these months and days by the number of months and days of benefits utilized, and this is done on a calculator at present, a slow process. The scenario is that a college student is awarded for example 24 months of scholarship entitlements, for the first semester they will utilize for example 2 months and 12 days (this would be the number of months/days for a specific semester, the days from the first day of classes to the last day of classes, and this range will vary from semester to semester). In this scenario all months always = 30 days (regardless of the month length, like February that only has 28 days, it would = 30 days etc.). It would be nice to take a given set of months and days of scholarship entitlements on a spreadsheet, then calculate how many months/days are left after a student uses an amount of months/days for a given semester, basically counting down the entitlements as the student works through their semesters until the entitlements are used up. To always show what is left after each semester. To have the number of days left also reflect in months (at 30 days each) and days. Is this possible in Excel? The goal is the put the calculator away and fast track to this number with minimal effort. I appreciate any feedback on how to do this, formulas etc. Thank You!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
If every Month is calculated at 30 days just convert 2 years to 30*12*2 = 720 days and then subtract your 2 months 12 days or (2*30)+12 from 720 = 648
then if you want to show months and days remaining you can =ROUNDDOWN(A1/30,0) for months conversion and =Mod(a1,30) for days 648 would be 21 M and 18 days.
 
Upvote 0
Tigger8,

Did this work for you?
 
Upvote 0

Excel 2012
ABCDEFGHIJKLMN
1YEARAwarded AmountAmount Remaining1st Qtr2nd Qtr3rd Qtr4th Qtr
2ID/NAMEMonthsMonthsDaysMonthsDaysMonthsDaysMonthsDaysMonthsDays
3Student 124Months1921212121314
4Student 248Months322012345678
5Student 324Months41687654321
6Student 448Months480
7Student 536Months360
8Student 636Months360
9Student 712Months120
10Student 812Months120
11Student 924Months240
12Student 1036Months360
Sheet1
Cell Formulas
RangeFormula
D3=ROUNDDOWN((B3*30-SUM(G3,I3,K3,M3)*30+SUM(H3,J3,L3,N3))/30,0)
D4=ROUNDDOWN((B4*30-SUM(G4,I4,K4,M4)*30+SUM(H4,J4,L4,N4))/30,0)
D5=ROUNDDOWN((B5*30-SUM(G5,I5,K5,M5)*30+SUM(H5,J5,L5,N5))/30,0)
D6=ROUNDDOWN((B6*30-SUM(G6,I6,K6,M6)*30+SUM(H6,J6,L6,N6))/30,0)
D7=ROUNDDOWN((B7*30-SUM(G7,I7,K7,M7)*30+SUM(H7,J7,L7,N7))/30,0)
D8=ROUNDDOWN((B8*30-SUM(G8,I8,K8,M8)*30+SUM(H8,J8,L8,N8))/30,0)
D9=ROUNDDOWN((B9*30-SUM(G9,I9,K9,M9)*30+SUM(H9,J9,L9,N9))/30,0)
D10=ROUNDDOWN((B10*30-SUM(G10,I10,K10,M10)*30+SUM(H10,J10,L10,N10))/30,0)
D11=ROUNDDOWN((B11*30-SUM(G11,I11,K11,M11)*30+SUM(H11,J11,L11,N11))/30,0)
D12=ROUNDDOWN((B12*30-SUM(G12,I12,K12,M12)*30+SUM(H12,J12,L12,N12))/30,0)
E3=MOD(B3*30-SUM(G3,I3,K3,M3)*30+SUM(H3,J3,L3,N3),30)
E4=MOD(B4*30-SUM(G4,I4,K4,M4)*30+SUM(H4,J4,L4,N4),30)
E5=MOD(B5*30-SUM(G5,I5,K5,M5)*30+SUM(H5,J5,L5,N5),30)
E6=MOD(B6*30-SUM(G6,I6,K6,M6)*30+SUM(H6,J6,L6,N6),30)
E7=MOD(B7*30-SUM(G7,I7,K7,M7)*30+SUM(H7,J7,L7,N7),30)
E8=MOD(B8*30-SUM(G8,I8,K8,M8)*30+SUM(H8,J8,L8,N8),30)
E9=MOD(B9*30-SUM(G9,I9,K9,M9)*30+SUM(H9,J9,L9,N9),30)
E10=MOD(B10*30-SUM(G10,I10,K10,M10)*30+SUM(H10,J10,L10,N10),30)
E11=MOD(B11*30-SUM(G11,I11,K11,M11)*30+SUM(H11,J11,L11,N11),30)
E12=MOD(B12*30-SUM(G12,I12,K12,M12)*30+SUM(H12,J12,L12,N12),30)



I created a simple layout for you hope this is what you are looking for.

~DR
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top