# Subtracting months and days in Excel

#### Tigger8

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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.

Tigger8,

Did this work for you?

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

Replies
3
Views
625
Replies
2
Views
282
Replies
8
Views
2K
Replies
0
Views
824
Replies
3
Views
1K

### Forum statistics

1,196,476
Messages
6,015,436
Members
441,895
Latest member
Zululander ### 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