How To Calculate Remainder of 30 Days

nutrastat

Board Regular
Joined
Nov 1, 2008
Messages
57
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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