# How To Calculate Remainder of 30 Days

#### nutrastat

##### Board Regular
Hello All,

I have a start date and end date, for an unknown period.
It could be 1 Jan 2021 to 24 Jan 2021 = 24 days
It could be 1 Jan 2021 to 3 June 2021 = XX days

I need to know the count less 30 days, so that the end result does not exceed 30 days.

I have done this with IF Statements, where under 30 = the original date period number of days, which in the above example is 24 days.
But to calculate an unknown period, and know the final number of days which are 30 or below I cannot fathom.
With the IF Statements I have set this for a maximum 4 months period, but it the period extends to 5 or 6 months, then I will need to increase the IF Statement to include this period.

I am sure that there is a simple way of doing this without a multiple IF Statement. Can anyone help me with this?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

=MIN(B1-A1,30)

#### nutrastat

##### Board Regular
Thank you for this response so promptly. Unfortunately when amount is above 30 it give me 30, and not the LOWER difference. My apologies for not making myself clearer. Let me try again...

15 Apr 2021 to 1 June 2021 (Calc Days = 47), within 30 days, Expected Result = 17
1 Apr 2021 to 15 June 2021 (Calc Days = 75), within 30 days, Expected Result = 15
1 May 2021 to 31 July 2021 (Calc Days 91), within 30 days, Expected Result = 1

It is BLOCKS of 30 days, and then to give the difference which will be equal to 30 days maximum, or below 30 days.

Hope the above is now a little clearer, and any help you can give would be appreciated.

#### FormR

##### MrExcel MVP
Hi, here is an option you can try.

Book1
ABC
115 Apr 202101 Jun 202117
201 Apr 202115 Jun 202115
301 May 202131 Jul 20211
Sheet1
Cell Formulas
RangeFormula
C1:C3C1=MOD(B1-A1,30)

#### nutrastat

##### Board Regular

Thank you very much once again.

Sorry, almost, but it does not work if the number is 30, it gives 0, which is correct.

I was not aware of this function. Looked it up, understood what is did, and this does work for me: =IF(B4-A4<=30,B4-A4,MOD(B4-A4,30))

Thank you very much for your help & assistance; it is appreciated.

#### FormR

##### MrExcel MVP
and this does work for me: =IF(B4-A4<=30,B4-A4,MOD(B4-A4,30))

Hi, you can achieve the same results with:
Excel Formula:
``=MOD((B4-A4)-1,30)+1``

Last edited:

#### nutrastat

##### Board Regular
Thank you, I will mark the above as the solution.

If you are ever down in Brighton, UK way I will buy you a beer!

Replies
5
Views
130
Replies
6
Views
135
Replies
1
Views
74
Replies
14
Views
244
Replies
5
Views
85

1,141,203
Messages
5,704,934
Members
421,372
Latest member
Jamie11

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